SQL Server Built-in Functions

SQL Server provides a rich set of built-in functions to perform various operations on data. These functions cover string manipulation, mathematical calculations, date and time handling, data type conversions, and more advanced functionalities.

SQL Server String Functions

Function	Description
`ASCII(character)`	Returns the ASCII value of a character.
`CHAR(code)`	Returns the character for a given ASCII code.
`CHARINDEX(substring, string)`	Finds the starting position of a substring within a string.
`CONCAT(string1, string2, ...)`/`string1 + string2 + ...`	Concatenates (joins) two or more strings.
`CONCAT_WS(separator, string1, string2, ...)`	Concatenates strings with a separator.
`DATALENGTH(expression)`	Returns the length of an expression in bytes.
`DIFFERENCE(string1, string2)`	Compares two SOUNDEX values.
`FORMAT(value, format)`	Formats a value using a specified format string.
`LEFT(string, length)`	Extracts a specified number of characters from the left of a string.
`LEN(string)`	Returns the length of a string (number of characters).
`LOWER(string)`	Converts a string to lowercase.
`LTRIM(string)`	Removes leading spaces from a string.
`NCHAR(code)`	Returns the Unicode character for a given code point.
`PATINDEX('%pattern%', string)`	Returns the starting position of a pattern in a string.
`QUOTENAME(string, quote_char)`	Adds delimiters to a string to make it a valid SQL identifier.
`REPLACE(string, old_substring, new_substring)`	Replaces occurrences of a substring with another substring.
`REPLICATE(string, number)`	Repeats a string a specified number of times.
`REVERSE(string)`	Reverses a string.
`RIGHT(string, length)`	Extracts a specified number of characters from the right of a string.
`RTRIM(string)`	Removes trailing spaces from a string.
`SOUNDEX(string)`	Returns a phonetic representation of a string.
`SPACE(number)`	Returns a string with a specified number of spaces.
`STR(number)`	Converts a number to a string.
`STUFF(string, start, length, replacement)`	Replaces part of a string with another string.
`SUBSTRING(string, start, length)`	Extracts a substring from a string.
`TRANSLATE(string, characters_to_replace, replacement_characters)`	Replaces characters in a string based on a mapping.
`TRIM([{BOTH \| LEADING \| TRAILING} [characters]] FROM string)`	Removes leading and/or trailing spaces or characters.
`UNICODE(string)`	Returns the Unicode code point of the first character.
`UPPER(string)`	Converts a string to uppercase.

SQL Server Math/Numeric Functions

Function	Description
`ABS(number)`	Returns the absolute value of a number.
`ACOS(number)`	Returns the arccosine of a number.
`ASIN(number)`	Returns the arcsine of a number.
`ATAN(number)`	Returns the arctangent of a number.
`ATN2(y, x)`	Returns the arctangent of y/x.
`AVG(expression)`	Returns the average of values in a group.
`CEILING(number)`	Returns the smallest integer greater than or equal to a number.
`COUNT(*)`	Returns the number of rows in a table or result-set.
`COS(number)`	Returns the cosine of a number.
`COT(number)`	Returns the cotangent of a number.
`DEGREES(number)`	Converts radians to degrees.
`EXP(number)`	Returns e raised to the power of a number.
`FLOOR(number)`	Returns the largest integer less than or equal to a number.
`LOG(number)`/`LOG(number, base)`	Returns the natural logarithm or logarithm to a specified base.
`LOG10(number)`	Returns the base-10 logarithm of a number.
`MAX(expression)`	Returns the maximum value in a group.
`MIN(expression)`	Returns the minimum value in a group.
`PI()`	Returns the value of PI.
`POWER(number, power)`	Raises a number to a power.
`RADIANS(number)`	Converts degrees to radians.
`RAND()`	Returns a random number.
`ROUND(number, length)`	Rounds a number to a specified number of decimal places.
`SIGN(number)`	Returns the sign of a number (-1, 0, or 1).
`SIN(number)`	Returns the sine of a number.
`SQRT(number)`	Returns the square root of a number.
`SQUARE(number)`	Returns the square of a number.
`SUM(expression)`	Returns the sum of values in a group.
`TAN(number)`	Returns the tangent of a number.

SQL Server Date Functions

Function	Description
`CURRENT_TIMESTAMP`	Returns the current date and time.
`DATEADD(datepart, number, date)`	Adds a time/date interval to a date.
`DATEDIFF(datepart, startdate, enddate)`	Returns the difference between two dates.
`DATEFROMPARTS(year, month, day)`	Creates a date from year, month, and day values.
`DATENAME(datepart, date)`	Returns a part of a date as a string.
`DATEPART(datepart, date)`	Returns a part of a date as an integer.
`DAY(date)`	Returns the day of the month.
`GETDATE()`	Returns the current datetime.
`GETUTCDATE()`	Returns the current UTC datetime.
`ISDATE(expression)`	Checks if an expression is a valid date.
`MONTH(date)`	Returns the month of a date.
`SYSDATETIME()`	Returns the current datetime.
`YEAR(date)`	Returns the year of a date.

SQL Server Conversion and Advanced Functions

Function	Description
`CAST(expression AS datatype)`/`CONVERT(datatype, expression)`	Converts a value from one data type to another.
`COALESCE(expression1, expression2, ...)`	Returns the first non-NULL expression.
`CURRENT_USER`/`SESSION_USER`/`SYSTEM_USER`	Returns the current user name.
`IIF(condition, true_part, false_part)`	Returns one of two values based on a condition.
`ISNULL(expression, replacement)`	Replaces NULL values with a specified value.
`ISNUMERIC(expression)`	Checks if an expression is numeric.
`NULLIF(expression1, expression2)`	Returns NULL if two expressions are equal.
`SESSIONPROPERTY(option)`	Returns session settings.
`USER_NAME(id)`	Returns database user name for a given ID.