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)