Let us discuss about some very important SQL Server system functions in this article. We will talk about how to perform the tasks such as data type conversions, handling the null values, extracting specific parts of date or time values, calculating the string lengths, performing the aggregate calculations, and extracting the substrings. After understanding these SQL Server system functions, it will help us to optimize the database operations and extract useful information from our data.
Create a Sample Table for Demonstration
Let us create a sample table named as “EmP” and insert it with some values to understand the practical usage of the SQL Server system functions. Let’s first create a sample table called “EmP” with columns such as BatchID, Fname, LName, BirthDate, and Salary.
(
BatchID INT,
Fname VARCHAR(50),
LName VARCHAR(50),
BirthDate DATE,
Salary DECIMAL(10, 2)
);
GO
INSERT INTO EmP (BatchID, Fname, LName, BirthDate, Salary)
VALUES
(1, 'Ram', 'Nath', '1985-07-12', 50000),
(2, 'Moddhu', 'Das', '1990-02-15', 60000),
(3, 'Alex', 'Rozario', '1988-09-20', 55000),
(4, 'Tanmoy', 'Dutta', '1995-05-10', 45000),
(5, 'Piu', 'Saha', '1992-12-01', 70000);
Output:
1 Ram Nath 1985-07-12 50000.00
2 Moddhu Das 1990-02-15 60000.00
3 Alex Rozario 1988-09-20 55000.00
4 Tanmoy Dutta 1995-05-10 45000.00
5 Piu Saha 1992-12-01 70000.00
Types of System Functions in SQL Server
SQL Server provides an extensive library of system functions that serve to diverse data manipulation and analysis requirements. These functions are built-in features provided by the SQL Server and are available for use. Let us discuss about some very important SQL Server system functions that are used frequently.
1. CAST and CONVERT Functions
The CAST and CONVERT functions enable the data type conversion.
SELECT CAST (Salary AS INT) AS ConvertedSalary
FROM Employees;
GO
-- CONVERT function
SELECT CONVERT (VARCHAR, BirthDate, 106) AS FormattedBirthDate
FROM Employees;
Output:
ConvertedSalary
50000
60000
55000
45000
70000
** CONVERT function output**
FormattedBirthDate
12 Jul 1985
15 Feb 1990
20 Sep 1988
10 May 1995
01 Dec 1992
Here in the T-SQL query, the CAST function converts the “Salary” column to an integer. The CONVERT function formats the “BirthDate” column as a string using the format 106 (“dd month yyyy”).
2. ISNULL Function
This function replaces the NULL values with a specified value to ensure the consistent handling of nulls in query results.
FROM Employees;
Output:
Nath
Das
Rozario
Dutta
Saha
Here in the T-SQL query, the ISNULL function checks the “ProductName” column for NULL values. If a NULL value is found, it replaces it with “N/A”, providing a more meaningful output.
3. DATEPART and DATENAME Functions
These functions extract specific parts of a date or time value such as year, month, or day.
Suppose we need to extract our employee “birth year” and their “birth month”. So, we need to write the following T-SQL query:
FROM Employees;
Output:
1985 July
1990 February
1988 September
1995 May
1992 December
Here in the T-SQL query, the DATEPART function extracts the year from the “BirthDate” column, while the “DATENAME” function extracts the month name. This allows us for easy analysis and grouping of employees’ birthdates.
4. LEN Function
The LEN function in SQL server calculates the length of a string.
FROM Employees;
Output:
Ram 3
Moddhu 6
Alex 4
Tanmoy 6
Piu 3
Here in the T-SQL query, the LEN function calculates the length of the “Fname” column, providing the number of characters in each employee’s first name.
5. Aggregate Functions
Some useful aggregate functions in SQL Server are AVG, SUM, COUNT, MIN, and MAX. Let us discuss about each using the T-SQL query.
COUNT (*) AS TotalEmployees, MIN (Salary) AS MinSalary, MAX (Salary) AS MaxSalary
FROM Employees;
Output:
56000.000000 280000.00 5 45000.00 70000.00
Here in the T-SQL query, these aggregate functions calculate the various statistics on the “Salary” column including the average salary, total salary, total number of employees, minimum salary, and maximum salary.
6. SUBSTRING Function
The SUBSTRING function in SQL Server extracts only a portion of a string. Here, we extract only the first three letters from the “Fname” column.
FROM Employees;
Output:
Ram
Mod
Ale
Tan
Piu
Here in the T-SQL query, the SUBSTRING function extracts the first three characters from the “Fname” column, providing the reduced versions of each employee’s first name.
With the “Employees” table and these system methods in MS SQL, we may effectively manipulate, analyse, and modify the data. It makes the database maintenance and query operations more efficient. These features expand the SQL Server’s capabilities and versatility, making it a solid option for applications that rely on data.
Conclusion
The SQL Server system functions play a crucial role in data manipulation and analysis within the Microsoft SQL Server. We can perform various operations using functions such as CAST, CONVERT, ISNULL, DATEPART, DATENAME, LEN, and aggregate. These functions improve the functionality and efficiency of SQL Server, and make it a powerful tool for managing the data effectively.
