<< SQL Server Hosting - HostForLIFE :: A Fix To PolyBase Issue In SQL Server 2019 Developer Edition | SQL Server Hosting - HostForLIFE :: How To Insert A Default Value In SQL Statement? >>

SQL Server Hosting - HostForLIFE :: SQL Server Built-In Functions In SQL Server

January 3, 2022 07:30 by author

Peter

In this article, we will learn how to use Built-In functions in SQL Server.

What are Built-In functions?
A built-in function is a piece for programming that takes zero or more inputs and returns a value. There are many built-in functions in SQL Server. Here we are discussing about string, date, and time functions in SQL Server.

String Functions
ASCII()
ASCII (American Standard Code for Information Interchange) is a code that uses numbers to represent characters. This function returns the ASCII value for the specific character. To get the ASCII code of a character, we can call the ASCII() function. It takes a character as an argument and returns the ASCII code of the character. For example:
SELECT ASCII('A') AS [In ASCII Format]
Result: ‘65’   (ASCII code of “A” is 65)
SELECT ASCII('a') AS [In ASCII Format]
Result: ‘97’   (ASCII code of “A” is 65)

CHAR()
This function returns the character based on the ASCII code. It takes a numeric value as an argument and returns the character. For example:
SELECT CHAR(65) AS [In CAHR Format]
Result: ‘A’   (ASCII code of “A” is 65)
SELECT CHAR(85) AS [In CHAR Format]
Result: ‘U’   (ASCII code of “U” is 85)

CHARINDEX()
This function returns the position of a substring in a string. This function return 0(Zero) if the substring is not found. It takes three arguments. Syntax,
CHARINDEX (substring, string) / CHARINDEX (substring, string, start_position)

In the above syntax “substring” is the string to search for. “string” is the string to be searched. “start_position” is the position where the search will start. This is an optional parameter. If it is zero or negative value, the search starts at the beginning of the string.

SELECT CHARINDEX('CHAR', 'SQL Server CHARINDEX() function examples') AS MatchPosition;
Result: ‘A’   (We get 20 as the index point of “CHAR”)
SELECT CHARINDEX('CHAR1', 'SQL Server CHARINDEX() function examples') AS MatchPosition;
Result: ‘A’   (We get 0 as the index point of “CHAR1”, because it’s not found in the above string)

SOUNDEX()& DIFFERENCE()
This function compares two SOUNDEX values, and returns an integer. The integer value indicates the match for the two SOUNDEX values from 0 to 4, where 0 indicates weak or no similarity and 4 indicates strong similarity.

The SOUNDEX() function converts a string to a four-character code. This function compares the similarity between strings in terms of their sounds when it is spoken.

For example,
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('55') soundex2,DIFFERENCE('Juice', '55');
Result: ‘J200, 0000, 0’   (Here SOUNDEX codes are totally different so we get 0)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Debezium') soundex2,DIFFERENCE('Juice', 'Debezium');
Result: ‘J200, D125, 1’   (Here SOUNDEX codes are near to each other so we get 1)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Apple') soundex2,DIFFERENCE('Juice', 'Apple');
Result: ‘J200, A140, 2’   (Here SOUNDEX codes are more near to each other so we get 2)
SELECT SOUNDEX('India') soundex1,SOUNDEX('Indian') soundex2,DIFFERENCE('India', 'Indian');
Result: ‘I530, I535, 3’   (Here SOUNDEX codes are near to similar so we get 3)
SELECT SOUNDEX('Juice') soundex1,SOUNDEX('Jucy') soundex2,DIFFERENCE('Juice', 'Jucy');
Result: ‘J200, J200, 4’   (Here SOUNDEX codes are same so we get 4)

FORMAT()
Using this function we can format a value with a specified format. This function can be applied to various types of values such as integers, floating-point numbers, date, or time. It takes three arguments or parameters. Syntax:

FORMAT(value, format, culture)
value: is the type of value to be formatted. It can be a number, a date or time.

format : it specifies the format you want to apply to the first argument.

culture : is optional parameter, specifies a culture.

SELECT FORMAT(9658254870, '###-###-####');
Result: ‘965-825-4870’   (We get the above number in “###-###-####” format)
DECLARE @Salary INT = 56.68;
SELECT @Salary, FORMAT(@Salary, N'F'), FORMAT(@Salary, N'C', N'en-IN');
Result: ‘56, 56.00, ₹ 56.00’
SELECT FORMAT(GETDATE(), 'yyyy-MMM-dd'), FORMAT(GETDATE(), 'dd-MM-yyyy');
Result: ‘2021-Dec-27, 27-12-2021’   (Here we get the same date in two different format)

LEFT()
This function is used to get a number of characters from a string starting from left. This function takes two arguments. The first argument specifies the original string and the second argument specifies the number of characters from the most-left. For example:
SELECT LEFT('Peter Scott', 4) AS [Name];
Result: ‘Peter’   (We get only 4 character of the string)
SELECT LEFT('Sql Server Function Examples', 13) AS [Example];
Result: ‘965 Built-In Functions’

LEN()
This function returns the length of a string. This function takes one argument as the string to be considered. For example:
SELECT LEN('Peter Scott') AS [Count1];
Result: ‘12’   (Returns total character of the string including space)
SELECT LEN('Built-In Functions LEN() in SQL Server – Part One') AS [Count2];
Result: ‘49’   (Returns total character of the string including space)

LOWER() & UPPER()
LOWER() converts a string to lower-case and UPPER() function converts a string to upper-case. These functions take one argument as a string.
SELECT LOWER('Peter Scott') AS [Name];
Result: ‘peter scott’   (Returns the given string in lower case)
SELECT UPPER('Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘BUILT-IN FUNCTIONS IN SQL SERVER – PART ONE’   (Returns the given string in upper case)

LTRIM() & RTRIM()
LTRIM function removes all leading spaces from a string starting from left and RTRIM() function removes all leading spaces from a string from right.
SELECT LTRIM('       Peter Scott') AS [Name];
Result: ‘Peter Scott’   (Remove all space from left side of the string)
SELECT RTRIM('       Peter Scott         ') AS [Name];
Result: ‘       Peter Scott’   (Remove space from right)
SELECT LTRIM(RTRIM('       Peter Scott         ')) AS [Name];
Result: ‘Peter Scott’   (Remove all space from both left and right side of the string)

PATINDEX()
The PATINDEX() function returns the first occurrence of a pattern in a string. It returns 0 if the pattern is not found. It takes two arguments. First one is the pattern to search and it must be surrounded by “%”. Here we can also use other wildcards like “%”, “_”, “[]”, “[^]”. The second one is the string to be searched.

SELECT PATINDEX('%Part%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ’36’   (we search the pattern %Part% in the specified string)
SELECT PATINDEX('%S_rver%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘27’   (we search the position for the pattern “S” followed by “rver” in the string)
SELECT PATINDEX('%[^0-9A-z]%', 'Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘6’   (Here we want to finds out the position of the character without an alphabet, number)

REPLACE()
This function replaces all occurrences of a substring within a string, with a new substring. This function takes three arguments. The first is the string that will be used as reference. The second argument is a character or a sub-string to look for in the first argument. The third argument is a character or a sub-string to replace the second argument. For example: If we want to replace “SQL Server” as “MS SQL Server” in the “'Built-In Functions in SQL Server – Part One'” string.

SELECT REPLACE('Built-In Functions in SQL Server – Part One', 'SQL Server', 'MS SQL Server') AS [Example];
Result: ‘Built-In Functions in MS SQL Server – Part One’   (Here we replace “SQL Server” with “MS SQL Server”)
SELECT REPLACE('Peter Scott Thomas', 'Scott', '') AS [Name];
Result: ‘Peter Thomas’   (Here we replace “Scott” with “” blank)

REPLICATE()
This function repeats a string with a specified number of times. It takes two arguments. The first argument is the string to repeat and the second argument is no of time to repeat the string. For example:

SELECT REPLICATE(' Built-In Functions in SQL Server', 4) AS [Example];
Result: ‘Built-In Functions in SQL Server Built-In Functions in SQL Server Built-In Functions in SQL Server Built-In Functions in SQL Server’   (Here we get the above string repeated 4 times.)

REVERSE()
This function reverses a string. It takes one argument as string to reverse.

For example,

SELECT REVERSE('Peter Scott') AS [Name];
Result: ‘ytnahoM timA’
SELECT REVERSE('Built-In Functions in SQL Server – Part One') AS [Example];
Result: ‘enO traP – revreS LQS ni snoitcnuF nI-tliuB’

SPACE()
This function returns a string of the specified number of space characters.

SELECT 'Peter' + SPACE(10) + 'Scott' AS [Name];
Result: ‘Peter          Scott’    (It added 10 spaces between the two strings)

STR()
This function converts a numeric value to a string value. It takes three arguments.
    The first argument is the number to convert to string.
    The second argument is the length of the returning string and default value is 10.
    The third argument is the number of decimals to display in the returning string and the default value is 0.

The second and the third arguments are optional. For example,

SELECT STR(10.513) result;
Result: ‘11’    (result is rounded because we didn’t pass the decimal places so it takes defaults as 0)
SELECT STR(10.513, 5, 2) result;
Result: ‘10.51’

STUFF()
This function removes a part of a string and then inserts another part into the string, in a specified position. It takes four arguments. The first argument is the string to be processed or modified. The second argument is the position in string to start deletion and insertion of some characters. The third argument is the number of characters to delete from string. The fourth argument is the new sub-string to replace into string at the start position. For example,

SELECT STUFF('My name is ', 11, 1, ' Peter Scott!');
Result: ‘My name is Peter Scott!’
SELECT STUFF('My name is ', 1, 1, ' Peter Scott!');
Result: ‘Peter Scott!y name is’

SUBSTRING()
This function extracts a substring with a specified length starting from a position in a string. It takes three arguments. The first argument is the string from which we extract a character or sub-string. The second argument specifies the position where the returned substring starts. The third argument is the number of characters of the substring to be returned ant it must be a positive number. For example:

SELECT SUBSTRING('Built-In Functions in SQL Server – Part One', 4, 16) AS ResultString;
Result: ‘lt-In Functions’    (we get the string starting index from 4 to no of 16 characters)
SELECT SUBSTRING('Built-In Functions in SQL Server – Part One', 10, 16) AS ResultString;
Result: ‘Functions in SQL’

Date and Time Function
CURRENT_TIMESTAMP

This function returns the current date and time, in a “YYYY-MM-DD hh:mm:ss.mmm” format. For example:

SELECT CURRENT_TIMESTAMP;
Result: ‘2021-12-27 13:43:09.640’   (Date Time in “YYYY-MM-DD hh:mm:ss.mmm” format)

DATEADD()
This function adds a time/date interval to a input date and then returns the modified date. It takes three arguments. The first argument is the time/date interval to which we will add value. The second argument is the number of interval to be added to first argument. It can be positive or negative number. The third argument is the date that will be modified.

For example,
SELECT DATEADD(YEAR, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2023-12-25 13:50:47.233’
SELECT DATEADD(MONTH, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2022-02-25 13:50:47.233’
SELECT DATEADD(DAY, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-27 13:50:47.233’
SELECT DATEADD(WEEK, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2022-01-08 13:50:47.233’
SELECT DATEADD(HOUR, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 15:50:47.233’
SELECT DATEADD(MINUTE, 2, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:52:47.233’
SELECT DATEADD(SECOND, 30, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:51:17.233’
SELECT DATEADD(MILLISECOND, 200, '2021-12-25 13:50:47.234') AS [DateAdd];
Result: ‘2021-12-25 13:50:47.433’

DATEDIFF()
This function returns the difference between two dates in years, months, weeks, etc. It takes three arguments.

The first argument is “datepart” which is the part of date like a year, a month, a week, a day etc.

The second and third arguments are “startdate” and “enddate” to be compared.

For example,
SELECT DATEDIFF(YEAR, '2020/10/13', '2021/10/13') AS [DiffYear];
Result: ‘2’
SELECT DATEDIFF(MONTH, '2020/10/13', '2021/09/13') AS [DiffMonth];
Result: ‘11’
SELECT DATEDIFF(HOUR, '2020/10/13 07:00', '2021/10/13 12:45') AS [DiffHour];
Result: ‘8765’

DATEFROMPARTS()
This function returns a date from the specified parts like year, month, and day values. For example:

SELECT DATEFROMPARTS(2021, 12, 31) AS [DateFromParts];
Result: ‘2021-12-31’

DATENAME() & DATEPART()
The DATENAME() and DATEPART() function returns a specified part of a date. But the DATENAME() function returns the result as string value and DATEPART() return the result as integer value.

SELECT DATENAME(MONTH, '2021/12/25') AS [DateNameMonth],
       DATENAME(WEEKDAY, '2021/12/25') AS [DateNameDay];
Result: ‘December, Saturday’
SELECT DATEPART(MONTH, '2021/12/25') AS [DatePartMonth],
       DATEPART(WEEKDAY, '2021/12/25') AS [DatePartDay];
Result: ‘12, 7’

DAY(), MONTH() & YEAR()
The DAY() function returns the day of the month for a specified date. The value is from 1 to 31.

The MONTH() function returns the month part for a specified date. The value is from 1 to 12.

The YEAR() function returns the year part for a specified date.

SELECT DAY('2021-12-25') AS [Day];
Result: ‘25’
SELECT MONTH('2021-12-25') AS [Month];
Result: ‘12’
SELECT YEAR('2021-12-25') AS [Year];
Result: ‘2021’

GETDATE(),GETUTCDATE() & SYSDATETIME()
The GETDATE() function returns the current database date and time, in a “YYYY-MM-DD hh:mm:ss.mmm” format.

The GETUTCDATE() function returns the current database date and time in UTC, in a “YYYY-MM-DD hh:mm:ss.mmm” format.

The SYSDATETIME() function returns the date and time of the computer where the SQL Server instance is running.

SELECT GETDATE() AS [GetDate], GETUTCDATE() AS [UTCDate], SYSDATETIME() AS [SysDateTime];
Result: ‘2021-12-27 15:06:35.127, 2021-12-27 09:36:35.127, 2021-12-27 15:06:35.1283952’

ISDATE()
This function checks an expression is valid date or not. It returns 1 if it is a valid date, otherwise 0.
SELECT ISDATE('2021-12-25') AS [STATUS];
Result: ‘1’ (returns 1 because it’s a valid date)
SELECT ISDATE('2021-15-25') AS [STATUS];
Result: ‘0’ (returns 0 because not a valid date)

In the above article, we learned how to use string, date and time built-in functions in SQL Server. Hope this will help the readers.