“SQL Functions: Comprehensive Guide with Examples for Effective Data Manipulation”
SQL Server String Functions
1.ASCII : Returns the ASCII value for the specific character
Syntax : ASCII(character)
“Example 1 :Using the ASCII function in a SELECT query is demonstrated here:
SELECT ASCII(‘A’) AS ascii_value;
This would yield the character “”AASCII “”‘s value, which is 65.
Example 2:The ASCII function can also be used to find the ASCII code for a specific location inside a string. For instance:
SELECT ASCII(‘Hello’, 3) AS ascii_value;
“
2.CHAR: Returns the character based on the ASCII code
Syntax : CHAR(code)
“Example 1:A character is created from an ASCII value using the SQL function CHAR. Here are some instances of the CHAR function in action:
SELECT CHAR(65) as ‘Character’;
Example 2:Given that 65 is the ASCII value for the letter “”A,”” this will produce the character “”A.””
SELECT CHAR(72) as ‘Character’, CHAR(101) as ‘Character’, CHAR(108) as ‘Character’, CHAR(108) as ‘Character’, CHAR(111) as ‘Character’
“
3.CHARINDEX:Returns the position of a substring in a string
Syntax : CHARINDEX(substring, string, start)
Example 1: Find the position of a character or string’s first occurrence in a string:
SELECT CHARINDEX(‘a’, ‘abcdef’) as Position;
Since the character “a” appears in the string “abcdef” at position 1, this will return 1.
Example 2:Find the location of a character or string’s nth occurrence in a string:
SELECT CHARINDEX(‘a’, ‘aabbaa’, 3) as Position;
4.CONCAT : Adds two or more strings together
Synntax : CHARINDEX(substring, string, start)
“Example 1: Find the position of a character or string’s first occurrence in a string:
SELECT CHARINDEX(‘a’, ‘abcdef’) as Position;
Since the character “”a”” appears in the string “”abcdef”” at position 1, this will return 1.
Example 2:Find the location of a character or string’s nth occurrence in a string:
SELECT CHARINDEX(‘a’, ‘aabbaa’, 3) as Position;
“
5.Concat with +: Adds two or more strings together
Syntax : CONCAT(string1, string2, …., string_n)
“Example1 : joining two strings together:
SELECT CONCAT(‘Hello’, ‘ World’) as Greeting;
Example 2: adding three strings together:
SELECT CONCAT(‘I love ‘, ‘SQL ‘, ‘tutorials’) as Statement;
“
6.CONCAT_WS : Adds two or more strings together with a separator
Syntax : string1 + string2 + string_n
“Example1 : String concatenation in SQL is accomplished using the || operator (two pipes). Here are a few instances:
SELECT ‘Hello’ || ‘ ‘ || ‘World’ AS greeting;
— Returns: ‘Hello World’
Example 2: Additionally, you can concatenate strings in SQL by using the CONCAT function. Here’s an illustration:
SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’) AS greeting;
— Returns: ‘Hello World’
“
7.DATALENGTH : Returns the number of bytes used to represent an expression
Syntax : DATALENGTH(expression)
“Example 1 :How many bytes were needed to represent the string “”Hello World””?
SELECT DATALENGTH(‘Hello World’);
Example 2: To get the amount of bytes needed to represent the string kept in the “”Name”” column:
SELECT DATALENGTH(Name) FROM Customers;
“
8.DIFFERENCE :Compares two SOUNDEX values, and returns an integer value
Syntax : DIFFERENCE(expression, expression)
Example1 : AVG: This function computes a numeric column’s average value. For instance:
SELECT AVG(salary) FROM employees;
Example2: The COUNT function counts the number of columns with non-null values or the number of rows in a table. For instance:
SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;
9.FORMAT : Formats a value with the specified format
Syntax : FORMAT(value, format, culture)
Example1 : Examples of how to use the FORMAT function are provided below:
SELECT FORMAT(12345.678, 2) AS ‘Formatted Number’;
Example 2 : This will yield the string ‘12,345.68’ as a result.
SELECT FORMAT(12345.678, 0) AS ‘Formatted Number’;
10.LEFT : Extracts a number of characters from a string (starting from left)
Syntax : LEFT(string, number_of_chars)
Example 1 : Following are some illustrations of how to employ the LEFT function:
SELECT LEFT(‘Hello World’, 5) as ‘Left Characters’;
The characters on the left will be returned as “Hello.”
Example 2 :The LEFT function can also be used with a column name as an argument:
SELECT LEFT(column_name, 3) as ‘Left Characters’
FROM table_name;
11.LEN : Returns the length of a string
Syntax : LEN(string)
Example1 : SELECT LEN(‘Hello, world!’);
Due to the string’s 13-character length, “Hello, world!” this would return the result 13.
Example 2 : SELECT LEN(”);
As there are no characters in the empty string, this would yield the result 0.
12.LOWER : Converts a string to lower-case
Syntax : LOWER(text)
Example 1: SELECT LOWER(‘HELLO WORLD’) FROM DUAL;
It will now say “Hello, world.”
Example 2: SELECT LOWER(first_name) FROM customers WHERE last_name = ‘Smith’;
For every row in the customers table where the last name is “Smith,” this will produce the first name column in lowercase.
13.LTRIM : Removes leading spaces from a string
Syntax : LTRIM(string)
Example 1: A string’s leading spaces are removed
SELECT LTRIM(‘ Hello, World!’) AS TrimmedString;
Output:
TrimmedString
————-
Hello, World!
Example 2: deleting the leading spaces from a column-stored string
SELECT LTRIM(Description) AS TrimmedDescription
FROM Products
WHERE ProductID = 1;
To eliminate any leading spaces from the Description, the LTRIM function is used in this example to pick the Description column from the Products table for the row where the ProductID is 1.
14.NCHAR : Returns the Unicode character based on the number code
Syntax : NCHAR(number_code)
Example 1: A number can be changed into a string of characters by:
SELECT NCHAR(65) AS “Number to Character”
This would get the answer “A.”
Example 2: Date to character string conversion:
SELECT NCHAR(GETDATE(), 8) AS “Date to Character”
By doing this, the current date in the format YYYYMMDD, such as 20230107, would be returned.
- PATINDEX : Returns the position of a pattern in a string
Syntax : PATINDEX(%pattern%, string)
Example 1: In the string “abcdefabc,” locate the first instance of the pattern “abc”:
SELECT PATINDEX(‘%abc%’, ‘abcdefabc’)
Given that the pattern “abc” begins at the beginning of the string, this would yield 1.
Example 2 : In the string “abcdefabc,” locate the first instance of the pattern “def”:
SELECT PATINDEX(‘%def%’, ‘abcdefabc’)
Given that the pattern “def” begins at position 4 in the string, this would return 4.
16.QUOTENAME : Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier
Syntax : QUOTENAME(string, quote_char)
Example 1 :
SELECT *
FROM [dbo].[Customers]
WHERE [CustomerName] = ‘John Smith’;
The table name [Customers] and the column name [CustomerName] are enclosed in square brackets in this example using the QUOTENAME function. This makes it more likely that the T-SQL query will be correctly interpreted, even if the table or column names coincide with SQL reserved words.
Example 2 :
SELECT QUOTENAME([CustomerName]) AS [Customer], SUM([TotalSales]) AS [Total]
FROM [dbo].[Customers]
GROUP BY [CustomerName]
HAVING SUM([TotalSales]) > 1000;
In this example, the column name [CustomerName], which is aliased as [Customer], is enclosed in square brackets using the QUOTENAME function. When the column name [TotalSales] is used in the SUM function and aliased as [Total], the function is also used to surround it in square brackets.
17.REPLACE :Replaces all occurrences of a substring within a string, with a new substring
Syntax : REPLACE(string, old_string, new_string)
Example 1 : In a column labelled “fruit,” swap out all instances of the word “apple” with the word “orange”:
SELECT REPLACE(fruit, ‘apple’, ‘orange’)
FROM fruit_table;
Example 2 : In a column labelled “fruit,” swap out all instances of the word “apple” with the word “orange”:
SELECT REPLACE(pet, ‘cat’, ‘dog’)
FROM pet_table;
18.REPLICATE : Repeats a string a specified number of times
Syntax : REPLICATE(string, integer)
Example 1 : Five times through a string:
SELECT REPLICATE(‘Hello ‘, 5)
Example 2 : Replicate the following string from a table column:
SELECT REPLICATE(string_column, 3) FROM table_name
19.REVERSE : Reverses a string and returns the result
Syntax : REVERSE(string)
Example 1 :
SELECT REVERSE(‘Hello, World!’) AS reversed_string;
The output from this would be as follows:
reversed_string
—————-
!dlroW ,olleH
Example 2 :
SELECT REVERSE(title) AS reversed_title
FROM books
WHERE author = ‘Jane Austen’;
All of Jane Austen’s books’ titles inverted would be returned if you did this.
20.RIGHT : Extracts a number of characters from a string (starting from right)
Syntax : RIGHT(string, number_of_chars)
Example 1: Find the final three characters in a string:
SELECT RIGHT(‘abcdef’, 3) FROM dual;
“def” will be returned.
Example 2 : Using a column name as the input, extract the final two characters of the given string:
SELECT RIGHT(column_name, 2) FROM table_name;
21.RTRIM : Removes trailing spaces from a string
Syntax : RTRIM(string)
Example 1: Getting rid of trailing spaces in a string
SELECT RTRIM(‘Hello World ‘);
With the trailing spaces omitted, this would return the string “Hello World.”
Example 2 : Taking away the last spaces in a column:
SELECT RTRIM(column_name) FROM table_name;
This would remove the trailing spaces from every value in the column name column and return them all.
22.SOUNDEX : Returns a four-character code to evaluate the similarity of two strings
Syntax : SOUNDEX(expression)
Example 1 : Find all names that are pronounced like “Smith”:
SELECT * FROM names WHERE SOUNDEX(name) = SOUNDEX(‘Smith’);
Example 2 : Search for all names that rhyme with “Smith” and begin with the letter “S”:
SELECT * FROM names WHERE SOUNDEX(name) = SOUNDEX(‘Smith’) AND name LIKE ‘S%’;
23.SPACE : Returns a string of the specified number of space characters
Syntax : SPACE(number)
Example 1: Adding a single space to a string:
SELECT ‘Hello’ || SPACE(1) || ‘World’;
This would result in the string ‘Hello World’ being returned.
Example 2 : Inserting a string with several spaces:
SELECT ‘Hello’ || SPACE(5) || ‘World’;
This would produce the string ‘Hello World,’ with five spaces between the words ‘Hello’ and ‘World’.
24.STR : Returns a number as string
Syntax : STR(number, length, decimals)
Example 1 : To convert a number to a string and choose it, do the following:
SELECT STR(12345) as number_string FROM dual;
This will yield a single row with a single column called number string that contains the string ‘12345’.
Example 2 : To convert a number in a table column to a string and select it, do the following:
SELECT STR(number_column) as number_string FROM my_table;
This will return a row for each row in my_ table, with a number_ string column holding the string representation of each row’s number _column value.
25.STUFF : Deletes a part of a string and then inserts another part into the string, starting at a specified position
Syntax : STUFF(string, start, length, new_string)
Example 1 : Insert a string at a certain place into another string:
SELECT STUFF(‘ABCDEFGHI’, 4, 0, ‘x’)
This would result in the string ‘ABCxDEFGHI’ being returned.
Example 2 :Replace some characters in a string with characters from another string:
SELECT STUFF(‘ABCDEFGHI’, 4, 3, ‘x’)
This would result in the string ‘ABCxGHI’ being returned.
26.SUBSTRING : Extracts some characters from a string
Syntax : SUBSTRING(string, start, length)
Example 1: Extracting a segment of a string:
SELECT SUBSTRING(‘Hello World’, 1, 5) as greeting;
This would result in the output ‘Hello’.
Example 2 : Extracting a chunk of a string beginning at a specific position:
SELECT SUBSTRING(‘Hello World’, 7) as greeting;
This would result in the output ‘World’.
27.TRANSLATE : Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.
Syntax : TRANSLATE(string, characters, translations)
Example 1: All occurrences of the letter “a” should be replaced by the letter “b”:
SELECT TRANSLATE(‘hello world’, ‘a’, ‘b’) AS modified_string;
This will return the string “hello world” with all occurrences of the letter “a” substituted by the letter “b,” resulting in “hebllo world”.
Example 2 : All occurrences of the letters “aeiou” should be replaced with the letter “z”:
SELECT TRANSLATE(‘hello world’, ‘aeiou’, ‘z’) AS modified_string;
This will return the string “hzllz wzrld” with all vowels replaced by the letter “z”.
28.TRIM : Removes leading and trailing spaces (or other specified characters) from a string
Syntax : TRIM([characters FROM ]string)
Example 1: To eliminate leading and trailing spaces from a string, do the following:
SELECT TRIM(‘ Hello, World! ‘);
‘Hello, World!’ will be returned.
Example 2 : Only the leading spaces should be removed:
SELECT TRIM(LEADING ‘ ‘ FROM ‘ Hello, World! ‘);
‘Hello, World!’ will be returned.
29.UNICODE :Returns the Unicode value for the first character of the input expression
Syntax : UNICODE(character_expression)
Example 1 : To get the Unicode value of the first character in a string:
SELECT UNICODE(‘A’) AS UnicodeValue;
Example 2: Return the Unicode value of the first character in a string column:
SELECT UNICODE(FirstName) AS UnicodeValue
FROM Employees;
30.UPPER : Converts a string to upper-case
Syntax : UPPER(text)
Example 1 : SELECT UPPER(‘hello world’) FROM dual;
This would result in the restoration of ‘HELLO WORLD.’
Example 2 : SELECT UPPER(name) FROM customers;
This would create a new column with the customers’ names in upper case.
SQL Server Math/Numeric Functions
- ABS : Returns the absolute value of a number
Syntax : ABS(number)
Example 1: Determine a number’s absolute value:
SELECT ABS(-5) as absolute_value;
Output:
absolute_value
5
Example 2 : Find the absolute value of a table column:
SELECT ABS(column_name) as absolute_value
FROM table_name;
32.ACOS : Returns the arc cosine of a number
Syntax : ACOS(number)
Example 1: To find the inverse cosine of 0.5:
SELECT ACOS(0.5)
Example 2: To find the inverse cosine of a column in a table, do the following:
SELECT ACOS(column_name) FROM table_name
33.ASIN : Returns the arc sine of a number
Syntax : ASIN(number)
Example 1: To find the arcsine of 0.5 in radians, do the following:
SELECT ASIN(0.5);
The answer would be 0.523599.
Example 2 : To find the arcsine of -0.5 degrees:
SELECT RADIANS_TO_DEGREES(ASIN(-0.5));
The result would be -30.
34.ATAN : Returns the arc tangent of a number
Syntax : ATAN(number)
Example 1: Determine the arctangent of a number:
SELECT ATAN(1.0) as arctangent;
This will give you the arctangent of 1.0, which is around 0.7853981633974483.
Example 2: Determine the arctangent of a table column:
SELECT ATAN(column_name) as arctangent
FROM table_name;
The arctangent of each value in the specified column will be returned.
35.ATN2 : Returns the arc tangent of two numbers
Syntax : ATN2(a, b)
Example 1 : SELECT ATN2(3, 4)
The arctangent of the quotient of 3 and 4 is roughly 0.93 in this example.
Example 2: SELECT ATN2(-3, 4)
The arctangent of the quotient of -3 and 4 is roughly -0.93 in this example.
36.AVG : Returns the average value of an expression
Syntax : AVG(expression)
Example 1: Determine the average compensation for all employees:
SELECT AVG(salary) FROM employees;
Example 2: Determine the average cost of all products:
SELECT AVG(price) FROM products;
37.CEILING : Returns the smallest integer value that is >= a number
Syntax : CEILING(number)
Example 1: Find the smallest integer larger than or equal to the average employee salary:
SELECT CEILING(AVG(salary)) FROM employees;
Example 2: Find the smallest integer bigger than or equal to a number’s square root:
SELECT CEILING(SQRT(16)) FROM dual;
38.COUNT : Returns the number of records returned by a select query
Syntax : COUNT(expression)
Example 1 : The number of rows in the ‘users’ table is returned:
SELECT COUNT(*) FROM users;
Example 2 : Return the number of users who have the following email domain:
SELECT COUNT(*) FROM users WHERE email LIKE ‘%@example.com’;
39.COS : Returns the cosine of a number
Syntax : COS(number)
Example 1 : SELECT COS(0) as Cosine;
— Output: 1
Example 2: SELECT COS(PI()/4) as Cosine;
— Output: 0.7071067811865475
40.COT : Returns the cotangent of a number
Syntax : COT(number)
Example 1 : To find the cotangent of 0.5 radians, use the following formula:
SELECT COT(0.5);
The answer is roughly 1.83048772171245.
Example 2 : To find the cotangent of a value in a table column, use the following formula:
SELECT COT(angle) FROM angles;
This would compute the cotangent of the angle column value for each row in the angles table.
41 .DEGREES :Converts a value in radians to degrees
Syntax : DEGREES(number)
Example 1: Converting PI from radians to degrees:
SELECT DEGREES(PI()) AS ‘Degrees’;
Example 2: Converting an angle measured in radians to degrees:
SELECT DEGREES(1.57) AS ‘Degrees’;
42.EXP : Returns e raised to the power of a specified number
Syntax : EXP(number)
Example 1: SELECT EXP(1) FROM dual;
This would return the value of “e” increased to the power of one, which is about 2.718.
Example 2: You can also use the EXP function in a WHERE clause to compare the function’s output to a table column.
SELECT * FROM table_name
WHERE column_name < EXP(2);
This would return all rows from the table where the “column name” column value is less than the value of “e” increased to the power of 2. (approximately 7.389).
- FLOOR :Returns the largest integer value that is <= to a number
Syntax : FLOOR(number)
Example 1 : SELECT FLOOR(2.8) AS result;
This SELECT statement would provide the result 2.
Example 2 : You can also pass a decimal number as an input to the FLOOR function:
SELECT FLOOR(-2.8) AS result;
This SELECT statement would yield a value of -3.
45.LOG : Returns the natural logarithm of a number, or the logarithm of a number to a specified base
Syntax : LOG(number, base) — Syntax for SQL Server
Example 1: To find the base-10 logarithm of a number, use the formula:
SELECT LOG(25) FROM DUAL;
The result would be 3.2188758248682, which is the base-10 logarithm of 25.
Example 2: To find the base-2 logarithm of a number, use the formula:
SELECT LOG(25, 2) FROM DUAL;
The result would be 4.6438561897747, which is the base-2 logarithm of 25.
46.LOG10 : Returns the natural logarithm of a number to base 10
Syntax : LOG10(number)
Example 1: Calculating a number’s base-10 logarithm:
SELECT LOG10(100) FROM table;
This will provide 2 because log10(100) = 2.
Example 2 : Calculating a column’s base-10 logarithm:
SELECT LOG10(column) FROM table;
The base-10 logarithm of each value in the column for each row in the table will be returned.
47.MAX : Returns the maximum value in a set of values
Syntax : MAX(expression)
Example 1 : To find the highest-paying employee in the “employees” table:
SELECT MAX(salary) FROM employees;
Example 2 : To discover the employee with the highest wage in the “employees” table, do the following:
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
48.MIN : Returns the minimum value in a set of values
Syntax : MIN(expression)
Example 1: Find the smallest value in a table.
SELECT MIN(col_name) FROM table_name;
Example 2 : Find the smallest value among a set of values.
SELECT MIN(1, 2, 3, 4, 5);
49.PI : Returns the value of PI
Syntax : PI()
Example 1: To determine the value of PI:
SELECT PI();
Example 2 : To find the circumference of a circle with a radius of 5, use the formula:
SELECT 2 * 5 * PI();
50.POWER : Returns the value of a number raised to the power of another number
Syntax : POWER(a, b)
Example 1 : To calculate the square of a number:
SELECT POWER(2, 2) AS result;
This would get the value 4.
Example 2 : To find the cube of a number, use the formula:
SELECT POWER(2, 3) AS result;
This would return the result 8.
51.RADIANS : Converts a degree value into radians
Syntax : RADIANS(number)
Example 1 : Converting an angle in degrees to radians:
SELECT RADIANS(45) as radians;
This will get the value 0.7853981633974483, which is the radian equivalent of 45 degrees.
Example 2 : Converting multiple angle values in degrees to radians:
SELECT RADIANS(45), RADIANS(90), RADIANS(180) as radians;
This will return the values 0.7853981633974483, 1.5707963267948966, and 3.141592653589793, which correspond to 45 degrees, 90 degrees, and 180 degrees in radians, respectively.
52.RAND :Returns a random number
Syntax : RAND(seed)
Example 1: To choose a random row from a table:
SELECT * FROM table_name ORDER BY RAND() LIMIT 1;
Example 2 : To select a random sample of rows from a table:
SELECT * FROM table_name WHERE RAND() < 0.5 LIMIT 100;
53.ROUND : Rounds a number to a specified number of decimal places
Syntax : ROUND(number, decimals, operation)
Example 1 : To the nearest whole number, round a number:
SELECT ROUND(2.5)
The answer to this query is 3.
Example 2 : Round a number to the nearest hundredth:
SELECT ROUND(2.678, 2)
The answer to this query is 2.68.
54.SIGN : Returns the sign of a number
Syntax : SIGN(number)
Example 1 : Determine the sign of a number:
SELECT SIGN(-5);
Because -5 is a negative number, this will yield -1.
Example 2 : Find the sign of a column in a table:
SELECT SIGN(col)
FROM table;
This will return the sign of each value in the table table’s col column.
55.SIN : Returns the sine of a number
Syntax : SIN(number)
Example 1 : In a SELECT statement, here’s an example of how to utilise the SIN function:
SELECT SIN(PI()/4) AS sine;
The sine of 45 degrees is 0.7071067811865476 when this is done.
Example 2 : In a WHERE clause, you can also use the SIN function to filter results based on the sine of a column.
SELECT * FROM my_table WHERE SIN(angle_column) > 0.5;
This will retrieve all rows from the my _table table with sines larger than 0.5 in the angle_column.
56.SQRT : Returns the square root of a number
Syntax : SQRT(number)
Example1 : The following is an illustration of how to use it in a SELECT statement:
SELECT SQRT(9) AS sqrt_9;
The result would be 3, which is the square root of 9.
Example 2 : The SQRT function can also be applied to a table column.
SELECT SQRT(column_name) AS sqrt_column_name
FROM table_name;
The square root of each value in the column _name column of the table_ name table would be returned.
57.SQUARE : Returns the square of a number
Syntax : SQUARE(number)
Example 1 : To determine a number’s square:
SELECT SQUARE(2) AS square;
As a result, 4 will be returned.
Example 2 : To determine the square of a column-stored number:
SELECT SQUARE(column_name) AS square
FROM table_name;
For each row in the table_ name table, this will return the square of the number kept in the column _name column.
58.SUM : Calculates the sum of a set of values
Syntax : SUM(expression)
Example 1 : SELECT SUM(column_name) FROM table_name;
The total of all the values in the chosen column will be returned.
Example 2 : SELECT SUM(column_name) FROM table_name WHERE condition;
This will give you the total of all the values in the chosen column that satisfy the criteria.
59.TAN : Returns the tangent of a number
Syntax : TAN(number)
Example 1 : Here is an illustration of how the TAN function could be used in a SELECT statement:
SELECT TAN(PI()/4) as tan_pi_4;
As a result, this would give the value of the tangent to PI/4 (45 degrees).
Example 2 : In your SELECT statements, you can also combine the TAN function with other operators and functions.
SELECT TAN(PI()/2) + 2 as tan_pi_2_plus_2 FROM my_table;
For each entry in the my _table table, this would return the value of the tangent of PI/2 (90 degrees) + 2.
60.CURRENT_TIMESTAMP: Returns the current date and time
Syntax : CURRENT_TIMESTAMP
Example 1 : To enter the current timestamp into a table, do the following:
INSERT INTO events (event_name, event_timestamp)
VALUES (‘Event 1’, CURRENT_TIMESTAMP);
Example 2: To add the current timestamp to a row:
UPDATE events
SET last_updated = CURRENT_TIMESTAMP
WHERE event_name = ‘Event 1’;
- DATEADD : Adds a time/date interval to a date and then returns the date
Syntax : DATEADD(interval, number, date)
Example 1 : To add one day to the current date, do the following:
SELECT DATEADD(day, 1, GETDATE())
Example 2 : Add three months to the current date:
SELECT DATEADD(month, 3, GETDATE())
62.DATEDIFF : Returns the difference between two dates
Syntax : DATEDIFF(interval, date1, date2)
Example 1 : To find the number of days between two dates, use the following formula:
SELECT DATEDIFF(day, ‘2022-07-01’, ‘2022-07-15’) as ‘Number of Days’;
Because there are 14 days between July 1st and July 15th, the result would be 14.
Example 2 : To find the number of weeks between two dates, use the following formula:
SELECT DATEDIFF(week, ‘2022-07-01’, ‘2022-07-15’) as ‘Number of Weeks’;
Because there are two full weeks between July 1st and July 15th, this would yield a result of two.
63.DATEFROMPARTS :Returns a date from the specified parts (year, month, and day values)
Syntax : DATEFROMPARTS(year, month, day)
Example 1 : Here’s an illustration of how to utilise the DATEFROMPARTS function:
SELECT DATEFROMPARTS(2022, 5, 15) AS ‘Date’;
This would result in the date 2022-05-15 being returned.
Example 2 : To filter results based on a date range, use the DATEFROMPARTS function in a SELECT statement with a WHERE clause.
SELECT * FROM Orders
WHERE OrderDate BETWEEN DATEFROMPARTS(2022, 1, 1) AND DATEFROMPARTS(2022, 12, 31)
This will return any orders placed in 2022.
64.DATENAME : Returns a specified part of a date (as string)
Syntax : DATENAME(interval, date)
Example 1 : To find the name of the month for a given date, use the following formula:
SELECT DATENAME(month, ‘2022-09-20’) AS “Month Name”;
Output: “September”
Example 2 : To find the day of the week for a given date, use the following formula:
SELECT DATENAME(weekday, ‘2022-09-20’) AS “Day of Week”;
Output: “Tuesday”
65.DATEPART :Returns a specified part of a date (as integer)
Syntax : DATEPART(interval, date)
Example 1 : Using a date, determine the year:
SELECT DATEPART(year, ‘2022-03-14’)
This will be back in 2022.
Example 2 : Take the month out of a date:
SELECT DATEPART(month, ‘2022-03-14’)
This will yield 3.
66.DAY : Returns the day of the month for a specified date
Syntax : DAY(date)
Example 1 : Extract the month’s day from a date in the ‘YYYY-MM-DD’ format:
SELECT DAY(‘2022-03-15’) AS day_of_month;
The above sentence would yield ’15’ as the month’s day.
Example 2 : Extract the month’s day from a date in the ‘DD/MM/YYYY’ format:
SELECT DAY(’15/03/2022′) AS day_of_month;
The above sentence would also yield ’15’ as the month’s day.
67.GETDATE : Returns the current database system date and time
Syntax : GETDATE()
Example 1 : In a SELECT statement, use the following syntax to retrieve the current date and time:
SELECT GETDATE() AS ‘Current Date and Time’;
Example 2 : To enter the current date and time into a table, do the following:
INSERT INTO table_name (date_column)
VALUES (GETDATE());
68.GETUTCDATE : Returns the current database system UTC date and time
Syntax : GETUTCDATE()
Example 1 : To select the current UTC date and time:
SELECT GETUTCDATE()
Example 2 : To format the current UTC date and time as a string, select the following:
SELECT CONVERT(nvarchar, GETUTCDATE(), 120)
69.ISDATE : Checks an expression and returns 1 if it is a valid date, otherwise 0
Syntax : ISDATE(expression)
Example 1 : To see if a string is a valid date, use the following formula:
SELECT ISDATE(‘2022-12-31’) AS IsValidDate;
To see if a string is a valid date, use the following formula:
Example 2 : To determine whether a string is a valid date in a given format:
SELECT ISDATE(‘2022/12/31’, ‘yyyy/mm/dd’) AS IsValidDate;
Because ‘2022/12/31’ is a valid date in the ‘yyyy/mm/dd’ format, this will likewise return 1.
- MONTH : Returns the month part for a specified date (a number from 1 to 12)
Syntax : MONTH(date)
Example 1 : In SQL, the MONTH function is used to retrieve the month from a date data. Here are some examples of how the MONTH function can be used:
SELECT MONTH(GETDATE()) AS ‘Current Month’;
The current month will be returned as an integer value (e.g., 1 for January, 2 for February, etc.).
Example 2 : SELECT MONTH(‘2022-07-15’) AS ‘Month’;
This function will return the month for the specified date (7 for July).
71.SYSDATETIME : Returns the date and time of the SQL Server
Syntax : SYSDATETIME()
Example 1 : SELECT SYSDATETIME();
This will return the current system date and time in the SQL Server instance’s local time. The output will be in the format ‘YYYY-MM-DD hh:mm:ss.nnnnnn’, where ‘nnnnnn’ denotes fractional seconds.
Example 2 : SELECT CAST(SYSDATETIME() AS DATE);
This will return the current system date in the SQL Server instance’s local time in the format ‘YYYY-MM-DD’.
72.YEAR : Returns the year part for a specified date
Syntax : YEAR(date)
Example 1 : Here are some examples of how to utilise the YEAR function:
SELECT YEAR(‘2022-03-01’) as year;
This would make 2022 the year.
Example 2 : SELECT * FROM events WHERE YEAR(event_date) = 2022;
This would return all entries from the events table with the year 2022 in the event date column.
73.CAST : Converts a value (of any type) into a specified datatype
Syntax : CAST(expression AS datatype(length))
Example 1: Convert a string to a date using the following formula:
SELECT CAST(‘2022-12-01’ AS DATE)
Example 2 : To convert an integer to a string, use the following formula:
SELECT CAST(12345 AS CHAR(10))
74.COALESCE : Returns the first non-null value in a list
Syntax : COALESCE(val1, val2, …., val_n)
Example 1 : In a list of values, find the first non-NULL value:
SELECT COALESCE(NULL, NULL, ‘a’, ‘b’) AS “first non-null value”;
Because it is the first non-NULL entry in the list, this would return ‘a’ as the result.
Example 2 : COALESCE is used to provide a default value:
SELECT COALESCE(NULL, ‘default value’) AS “default value”;
Because the first element in the list is NULL and the second value is not, the output would be ‘default value’.
75.CONVERT : Converts a value (of any type) into a specified datatype
Syntax : CONVERT(data_type(length), expression, style)
Example 1 : To convert a value from one data type to another, do the following:
SELECT CONVERT(INT, ’10’) AS int_value;
This yields the integer value 10.
Example 2 : To translate a value into a specific style:
SELECT CONVERT(NVARCHAR(20), GETDATE(), 100) AS date_in_us_format;
The current date in US format will be returned (e.g., Mar 3, 2021).
76.CURRENT_USER : Returns the name of the current user in the SQL Server database
Syntax : CURRENT_USER
Example 1 : Here’s an example of how the CURRENT USER function might be used in a SELECT statement:
SELECT CURRENT_USER();
This would return a string with the current user name and host name, such as ‘user_name@localhost’.
Example 2 :
The CURRENT _USER function can be be used in other statements, such as a WHERE clause, to filter the results based on the current user:
SELECT * FROM users WHERE username = CURRENT_USER();
This would return all entries from the users table whose username column matches the current user name.
77.IIF: Returns a value if a condition is TRUE, or another value if a condition is FALSE
Syntax : IIF(condition, value_if_true, value_if_false)
Examples 1 :
To return “Yes” if the “IsActive” column has a value of 1, and “No” if it has a value of 0:
SELECT IIF(IsActive = 1, ‘Yes’, ‘No’) AS Active
FROM Customers
Example 2 : To return the value in column “Quantity” if it is greater than 0, and 0 if it is less than or equal to 0, use the following formula:
SELECT IIF(Quantity > 0, Quantity, 0) AS Quantity
FROM Orders
78.ISNULL Return a specified value if the expression is NULL, otherwise return the expression
Synax : ISNULL(expression, value)
Example 1 : To check for NULL values in a column and replace them with 0:
SELECT ISNULL(column_name, 0) FROM table_name;
Example 2 : To check a variable for NULL values and replace them with the default value:
DECLARE @var INT;
SET @var = NULL;
SELECT ISNULL(@var, 0);
79.ISNUMERIC : Tests whether an expression is numeric
Syntax : ISNUMERIC(expression)
Example 1: To see if a table column is numeric, do the following:
SELECT * FROM table_name
WHERE ISNUMERIC(column_name) = 1
Example 2 : To determine whether a string value is numerical:
SELECT ISNUMERIC(‘1234’) AS “Is Numeric”
This query would return “1” because the string “1234” is a valid numeric value.
80.NULLIF :Returns NULL if two expressions are equal
Syntax : NULLIF(expr1, expr2)
Example 1 : Using a SELECT command to compare two column values:
SELECT NULLIF(col1, col2)
FROM table_name;
Example 2 : Using a column value to compare to a literal value:
SELECT NULLIF(col1, ‘value’)
FROM table_name;
81.SESSION_USER : Returns the name of the current user in the SQL Server database
Syntax : SESSION_USER
Example 1 : In a SELECT statement, here’s an example of how to use the SESSION USER function:
SELECT SESSION_USER;
This will retrieve the current user name for the session.
Example 2 :
You can also utilise the SESSION _USER method in a more complicated query, such as:
SELECT * FROM users WHERE username = SESSION_USER;
This will return all entries from the users table where the username field equals the current session user name.
82.SESSIONPROPERTY Returns the session settings for a specified option
Syntax : SESSIONPROPERTY
Example 1: To return the current database’s compatibility level:
SELECT SESSIONPROPERTY(‘compatibility_level’) AS [Compatibility Level];
Example 2 : To get the current collation of the current SQL Server instance:
SELECT SESSIONPROPERTY(‘collation’) AS [Collation];
83.SYSTEM_USER Returns the login name for the current user
Syntax : SYSTEM_USER
Example 1 : AVG() calculates the average value of a numerical column.
SELECT AVG(price) FROM products;
Example 2 : COUNT() – This function returns the number of rows in a table.
SELECT COUNT(*) FROM customers;
84.USER_NAME Returns the database user name based on the specified id
Syntax : USER_NAME(id_number)
Example 1 : AVG() calculates the average value of a numerical column.
SELECT AVG(price) FROM products;
Example 2 : COUNT() – This function returns the number of rows in a table.
SELECT COUNT(*) FROM customers;