SQL Server has many built-in functions, the given following SQL server string functions process on an input string and return a string or numeric value. these references also contain the string, date, conversion, and few advanced functions.
SQL Server String Functions
Function |
Description |
ASCII |
Its used to find the ASCII value of a character means Returns the ASCII value for the specific character. |
CHAR |
Convert an ASCII value to a character means Returns the character based on the ASCII code |
CHARINDEX |
This function is used to returns the position of a substring in a string means that Search for a substring inside a string starting from a specified location and returns the position of the substring. |
CONCAT |
Adds two or more strings together or Join two or more strings into one string |
CONCAT WITH + |
It is used to Adds two or more strings together |
CONCAT WS |
Adds two or more strings together with a separator means Concatenate multiple strings with a separator into a single string |
DATALENGTH |
This function is used to returns the number of bytes used to represent an expression |
DIFFERENCE |
Compares two SOUNDEX values, and returns an integer value i.e. it is used to Compare the SOUNDEX() values of two strings |
FORMAT |
This function Return a value formatted with the specified format and optional culture |
LEFT |
Extracts a given number of characters from a string starting from left |
LEN |
Return a number of characters of a character string i.e. Return the length of a string |
LOWER |
This function is used to converts a string to lower-case |
LTRIM |
This function is used to return a new string from a specified string after removing all leading blanks |
NCHAR |
Returns the Unicode character based on the number code as defined by the Unicode standard |
PATINDEX |
It is used for returns the starting position of the first occurrence of a pattern in a string |
QUOTENAME |
A function is used to returns a Unicode string with the delimiters additional to form the input string a valid SQL Server delimited identifier. |
REPLACE |
Replace all occurrences of a substring, among a string, with another substring |
REPLICATE |
A function Return a string repeated a specific number of times |
REVERSE |
returns the result the reverse order of a character string |
RIGHT |
Extracts a given number of characters from a string starting from the right |
RTRIM |
Return a new string from a specified string after removing all trailing spaces from a string |
SOUNDEX |
Return a four-character (SOUNDEX) code to evaluate the similarity of two strings |
SPACE |
A function is used to returns a string of the specified number of space characters |
STR |
This function is used to returns the character data converted from numeric data |
STUFF |
Deletes a part of a string and then inserts another part into the string, beginning at a specified |
STRING_AGG |
A function is used for concatenating rows of strings with a specified separator into a new string |
STRING_ESCAPE |
Escapes special characters in a string and returns a new (replacement) string with escaped characters |
STRING_SPLIT |
A table-valued function that splits a string into rows of substrings which is based on a specified separator. |
SUBSTRING |
Extract a substring within a string beginning or starting from a specified location with a specified length |
TRANSLATE |
Replace several (many) single-characters, one-to-one translation in one operation. |
TRIM |
Return a new string from a specified string when removing all leading and trailing blanks |
UNICODE |
This function returns the integer value, therefore, as defined by the Unicode standard, of a character |
UPPER |
It is used to convert a string to uppercase |
SQL Server Date Functions
Function |
Description |
CURRENT_TIMESTAMP |
It functions returns the current date and time |
DATEADD |
This is used to adds a time/date interval to date and then it returns the date |
DATEDIFF |
Its returns the difference between two dates |
DATEFROMPARTS |
This function returns a date from the specified parts (year, month, and day values) |
DATENAME |
This function return returns a specified part of a date (as string) |
DATEPART |
This function returns a specified part of a date (as integer) |
DAY |
It returns the day of the month for a specified date |
GETDATE |
This function returns the current database system date and time |
GETUTCDATE |
This function returns the current database system UTC date and time |
ISDATE |
Checks an expression and returns 1 (one) if it’s a valid date, otherwise 0 (zero) |
MONTH |
This function returns the month part for a specified date (a number from one to 12) |
SYSDATETIME |
This function returns the date and time of the SQL Server |
YEAR |
Its returns the year part for a specified date |
SQL Server Numeric/Math Functions
Function |
Description |
ABS |
It returns the absolute value of a number |
ACOS |
Returns the arccosine of a number |
ASIN |
Returns the arcsine of a number |
ATAN |
Returns the arctangent of a number |
ATN2 |
Returns the arctangent of two numbers |
AVG |
Returns the average value of an expression |
CEILING |
This function returns the smallest integer value that is >= a number |
COUNT |
Returns the number of records returned by a selected query |
COS |
It will return the cosine of a number |
COT |
It returns the cotangent of a number |
DEGREES |
This function converts a value in radians to degrees |
EXP |
Returns e raised to the power of a specified number |
FLOOR |
It will return the largest integer value that’s <= to a number |
LOG |
Returns the logarithm of a number to a specified base or the natural logarithm of a number |
LOG10 |
Returns the natural logarithm of a number to base 10 (ten) |
MAX |
It returns the maximum value in a set of values |
MIN |
It returns the minimum value in a set of values |
PI |
It function returns the value of PI |
POWER |
This function returns the value of a number raised to the power of another number |
RADIANS |
This function converts a degree value into radians |
RAND |
Returns the random number |
ROUND |
This function rounds the number to a specified number of decimal places |
SIGN |
It returns the sign of a number |
SIN |
The returns the sine of a number |
SQRT |
The function returns the square root of a number |
SQUARE |
This function returns the square of a number |
SUM |
The Function calculates the sum of a set of values |
TAN |
This function returns the tangent of a number |
SQL Server Advanced Functions
Function |
Description |
CAST |
Converts a value (such as any type) into a specified datatype |
COALESCE |
The function returns the first non-null value in a list |
CONVERT |
Converts a value (such as any type) into a specified datatype |
CURRENT_USER |
Its function will return the name of the current user in the SQL Server database |
IIF |
It returns the value if a condition is TRUE, or another value if a condition is FALSE |
ISNULL |
The function returns a specified value if the expression is NULL, otherwise, return the expression |
ISNUMERIC |
Tests whether an expression is numeric |
NULLIF |
The function returns NULL if two expressions are equal |
SESSION_USER |
Its function will return the name of the current user in the SQL Server database |
SESSIONPROPERTY |
The function returns the session settings for a specified option |
SYSTEM_USER |
The function returns the login name for the current user |
USER_NAME |
The function returns the database user name based on the specified id |
Related Posts