Summary -
In this topic, we described about the SQL Server Functions Reference in detail.
SQL Server has many built-in functions like string, numeric, date, and advanced functions that are listed as follows –
SQL Server String Functions –
| Function | Description | Syntax |
|---|---|---|
| ASCII() | It returns ASCII value for the specified character. | ASCII( character) |
| CHAR() | It returns the character value for the specified ASCII value. | CHAR(ASCII value) |
| CHARINDEX() | It returns the value of position of sub string by searching in the string. | CHARINDEX('sub string', 'string', start value) |
| CONCAT() | This function add the two or more strings together to form one string. | CONCAT('string1', 'string2', 'string3',..) |
| Concat with + | By using + operator we can add two or more strings together. | 'string1'+ 'string2' + 'string3',.. |
| CONCAT_WS() | It adds two or more strings together with a separator. | CONCAT_WS(separator, 'string1', 'string2', 'string3',..) |
| DATALENGTH() | It returns count of the length of the expression. | DATALENGTH('expression') |
| SOUNDEX() | It returns the Four-character code in result set. It is used to check the similarity between two expressions. | SOUNDEX('expression') |
| DIFFERENCE() | It returns an integer value by comparing two Soundex values. The integer value vary from 0 to 4. 0 indicates weak matching, 4 indicates strong matching of two Soundex values. | DIFFERENCE('expression1', 'expression2') |
| FORMAT() | It Formats date or time or numeric values into required format. | FORMAT(value, Format, culture) |
| LEFT() | It returns the specified extracted characters in result set. It extracts from left of the string. | LEFT('expression', no_of_chars) |
| RIGHT() | It returns the specified extracted characters in result set. It extracts from right of the string. | RIGHT('expression', no_of_chars) |
| LEN() | It returns the length of the specified string. | LEN('expression') |
| LOWER() | It returns specified expression in lower case in result set. | LOWER('expression') |
| UPPER() | It returns the specified expression in upper case in result set. | UPEER('expression') |
| LTRIM() | It trims the starting spaces on string and returned it in result set. | LTRIM('expression') |
| RTRIM() | It trims the ending spaces on string and returned it in result set. | RTRIM('expression') |
| NCHAR() | It returns Unicode character for the specified number code value. | NCHAR(number_code) |
| PATINDEX() | It returns the position of pattern in the string. | PATINDEX('PATTERN', string) |
| QUOTENAME() | It returns specified Unicode character data with delimiters. | QUOTENAME(string, quote_char) |
| REPLACE() | It replaces the substring in a string with another new sub string. | REPLACE(String, old_sub_string, new_sub_string) |
| REPLICATE() | It repeats the string in result set with specified number of times. | REPLICATE('string', integer) |
| REVERSE()) | It returns the reversed specified string in result set. | REVERSE('string') |
| SPACE( ) | It returns the string in result set with specified number of spaces | SPACE (number) |
| STUFF() | It delete the specified characters in the string and insert another new characters in those paces. | STUFF('string', ) |
| STR() | It returns the numerical value into string format. | STR(number, no_of_digits,no_of_decimals) |
| SUBSTRING() | It returns the specified extract characters in result set from the string. | SBSTRING('string', position, no_of_charactrers) |
| TRIM() | It delete the starting spaces present in the string or specified characters in the string. | TRIM([Characters from ] 'string') |
| TRANSLATE() | It returns first argument string value in result set when the second argument string characters replaced with third argument string characters. | TRANSLATE('string1', 'string2', 'string3') |
| UNICODE () | It returns an integer value i.e. Unicode value for the specified first character in the expression. | UNICODE('expression') |
SQL Server mathematical functions –
| Function | Description | Syntax |
|---|---|---|
| ACOS() | It returns specified cosine value into radians i.e. angle in radians. | ACOS(float_expression); |
| ASIN() | It returns specified sine value in to radians i.e. angle in radians. | ASIN(float_expression); |
| ATAN() | It returns specified tangent value in radians i.e. angle in radians. | ATAN(float_expression); |
| ATN2() | It returns specified two tangent values in radians i.e. angle in radians. | ATN2(float_expression, float_expression); |
| COS() | It returns cosine value for the specified angle in radians in specified expression. | COS(float_expression); |
| COT() | It returns cotangent value for the specified angle in radians in specified expression. | COT(float_expression); |
| TAN() | It returns Tangent value for the specified angle in radians in specified expression. | TAN(float_expression); |
| SIN()) | It returns Sine value for the specified angle in radians in specified expression. | SIN(float_expression); |
| DEGREES() | It returns value in degree for specified value of angle in radians. It returns numeric value in result set i.e. FLOAT type. | DEGREES(number); |
| RADIANS() | It returns value in radians for specified value of angle in degrees. It returns numeric value in result set i.e. FLOAT type. | RADIANS(number); |
| EXP() | It returns the exponential value for the specified FLOAT value. specified value is in FLOAT or it implicitly convert to that type FLOAT. It uses the constant e (2.718281…) as the base. | EXP(float_expression); |
| LOG() | It returns the natural logarithm of specified number or logarithm of a required number to specified base. | LOG(number, base); |
| LOG10() | It returns the natural logarithm of specified number to the base of 10. It returns FLOAT value in the result set. | LOG10(float_expression); |
| PI() | It returns the constant value of PI. Return type of PI is FLOAT. | PI(); |
| POWER() | It returns the value of one value to the power of another value. The value and returned value is either any datatype but not bit datatype. | POWER(X, Y); |
| SQRT() | It returns the square root value of specified value. It returns either INT or FLOAT datatype based on the specified value. The specified value should be positive number. | SQRT(number); |
| SQUARE() | It returns the square value of specified number. It returns either INT or FLOAT datatype based on the specified value. | SQUARE(number); |
| CEILING() | It returns the next highest integer value for the specified decimal value. Specified value is in either positive or negative. | CEILING(number); |
| FLOOR() | It returns the next lowest integer value for the specified decimal value. Specified value is in either positive or negative. | FLOOR(number); |
| ROUND() | It returns the nearest round value for the specified decimal values. | ROUND(number); |
| ABS() | This function returns absolute value of a number. | ABS(number); |
| SIGN() | This function returns +1 if the specified value is positive. returns -1 if the specified value is negative. returns 0 if the specified value is zero. | SIGN(number); |
| RAND() | This function returns a random number in the result set. | RAND(Seed); |
SQL Server date functions –
| Function | Description | Syntax |
|---|---|---|
| CURRENT_TIMESTAMP | It returns the current date and time without offset time zone. | CURRENT_TIMESTAMP |
| GETUTCDATE() | It returns the current UTC timestamp date and time in result set. | GETUTCDATE() |
| GETDATE() | It returns the current date and time of the current operating system in result set. | GETDATE() |
| SYSDATETIME() | It returns the current date and time of current operating system with more seconds precision than GETDATE( ). | SYSDATETIME() |
| SYSUTCDATETIME() | It returns the current UTC timestamp date and time in result set. | SYSDATETIME() |
| SYSDATETIMEOFFSET() | It returns the current date and time with time zone offset in result set. | SYSDATETIMEOFFSET() |
| DATENAME() | It returns character string of the specified part of the date in result set. | DATENAME(date_part, input_date) |
| DATEPART() | It returns the integer part of specified part of the date. | DATEPART(date_part, input_date) |
| DAY() | DAY extracts day from specified DATE.. | DAY(input_date) |
| MONTH() | MONTH extracts month from specified date. | MONTH(input_date) |
| YEAR() | YEAR extracts year from specified date. | YEAR(input_date) |
| DATEDIFF() | It returns an integer value in result set i.e. difference between two dates. | DATEDIFF(date_part, first_date, second_date) |
| DATEADD() | It returns the modified data value by adding integer value to the specified part of the DATE. | DATEADD(date_part, value, input_date) |
| EOMONTH() | It returns the last day of month of the specified date. | EOMONTH(input_date, offset_value) |
| SWITCHOFFSET() | It returns the modified date value by replacing time zone of specified date value with required time zone value. | SWITCHOFFSET(expression, new_time_zone) |
| TODATETIMEOFFSET() | It returns modified value by modifying datetime value with time zone in result set. | TODATETIMEOFFSET(expression, time_zone) |
| DATEFROMPARTS() | It returns valid DATE from specified values.. | DATEFROMPARTS(year, month, day) |
| DATETIME2FROMPARTS() | It returns valid date with time in result set. | DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fraction, precision) |
| DATETIME2FROMPARTS() | It returns valid date with time and time zone in result set. | DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fraction, hour_offset, minute_offset, precision) |
| TIMEFROMPARTS() | It returns full time value from the specified values. | TIMEFROMPARTS(hour, minute, seconds, fraction, precision) |
| ISDATE() | It checks whether the specified date and time are valid or not. It returns 1, if it is valid or returns 0,if it is not valid. | ISDATE(expression) |
SQL Server Advanced functions –
| Function | Description | Syntax |
|---|---|---|
| CAST | It converts value of any datatype to specified datatype. | CAST(expression AS datatype(length)) |
| COALESCE | It returns first non-NULL values in the list. | COALESCE(value1, value2,..valuen) |
| CONVERT | It converts value of any datatype to specified datatype. | CONVERT(datatype(length), expression, style) |
| CURRENT_USER | It returns the name of the current user in SQL server database. | CURRENT_USER |
| IIF | It returns one value, If specified expression is TRUE otherwise it returns another value. | IIF(expression, True_value, false_value) |
| ISNULL | It returns a specified value if the expression is NULL, Otherwise it returns expression. | ISNULL(expression, value) |
| ISNUMERIC | It returns an integer value in result set for specified expression. It returns 1, If specified value is numeric otherwise it returns 0. | ISNUMERIC(expression) |
| NULLIF | It returns NULL value if the specified two expressions have same value, Otherwise it returns first expression. | NULLIF(expression1, expression2) |
| SESSION_USER | It returns the name of the current user. It has no arguments. | SESSION_USER |
| SESSIONPROPERTY | It returns session settings for the specified option. | SESSIONPROPERTY(option) |
| SYSTEM_USER | It returns the login name of the current user in the result set. | SYSTEM_USER |
| USER_NAME | It returns the data base user name based on the specified id. | USER_NAME(id_number) |