SQL Server Functions

SQL Scalar functions

UCASE() – Converts a field to upper case

LCASE() – Converts a field to lower case

MID() – Extract characters from a text field

LEN() – Returns the length of a text field

ROUND() – Rounds a numeric field to the number of decimals specified

NOW() – Returns the current system date and time

FORMAT() – Formats how a field is to be displayed

SQL String functions

ASCII()- Returns the ASCII value for the specific character

CHAR()- Returns the character based on the ASCII code

CHARINDEX ()- Returns the position of a substring in a string

CONCAT()- Adds two or more strings together

CONCAT_WS()- Adds two or more strings together with a separator

DATALENGTH()- Returns the number of bytes used to represent an expression

DIFFERENCE()- Compares two SOUNDEX values, and returns an integer value

FORMAT()- Formats a value with the specified format

LEFT()- Extracts a number of characters from a string (starting from left)

LEN()- Returns the length of a string

LOWER()- Converts a string to lower-case

LTRIM()- Removes leading spaces from a string

NCHAR()- Returns the Unicode character based on the number code

PATINDEX()- Returns the position of a pattern in a string

QUOTENAME()- Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier

REPLACE()-  Replaces all occurrences of a substring within a string, with a new substring

REPLICATE()- Repeats a string a specified number of times

REVERSE()-  Reverses a string and returns the result

RIGHT()- Extracts a number of characters from a string (starting from right)

RTRIM()- Removes trailing spaces from a string

SOUNDEX()- Returns a four-character code to evaluate the similarity of two strings

SPACE()- Returns a string of the specified number of space characters

STR()- Returns a number as string

STUFF()- Deletes a part of a string and then inserts another part into the string, starting at a specified position

SUBSTRING ()- Extracts some characters from a string

TRANSLATE()- Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.

TRIM()- Removes leading and trailing spaces (or other specified characters) from a string

UNICODE()-  Returns the Unicode value for the first character of the input expression

UPPER()- Converts a string to upper-case

SQL Server Math/Numeric Functions

ABS()- Returns the absolute value of a number

ACOS()- Returns the arc cosine of a number

ASIN()- Returns the arc sine of a number

ATAN()- Returns the arc tangent of a number

ATN2()- Returns the arc tangent of two numbers

AVG()- Returns the average value of an expression

CEILING()- Returns the smallest integer value that is >= a number

COUNT()- Returns the number of records returned by a select query

COS()- Returns the cosine of a number

COT()-            Returns the cotangent of a number

DEGREES()-  Converts a value in radians to degrees

EXP()- Returns e raised to the power of a specified number

FLOOR()- Returns the largest integer value that is <= to a number

LOG()- Returns the natural logarithm of a number, or the logarithm of a number to a specified base

LOG10()- Returns the natural logarithm of a number to base 10

MAX()- Returns the maximum value in a set of values

MIN()- Returns the minimum value in a set of values

PI()- Returns the value of PI

POWER()- Returns the value of a number raised to the power of another number

RADIANS()-  Converts a degree value into radians

RAND()- Returns a random number

ROUND()- Rounds a number to a specified number of decimal places

SIGN()- Returns the sign of a number

SIN()-  Returns the sine of a number

SQRT()- Returns the square root of a number

SQUARE()- Returns the square of a number

SUM()- Calculates the sum of a set of values

TAN()- Returns the tangent of a number

SQL Server Date Functions

CURRENT_TIMESTAMP()- Returns the current date and time

DATEADD()- Adds a time/date interval to a date and then returns the date

DATEDIFF()- Returns the difference between two dates

DATEFROMPARTS()- Returns a date from the specified parts (year, month, and day values)

DATENAME()- Returns a specified part of a date (as string)

DATEPART()- Returns a specified part of a date (as integer)

DAY()- Returns the day of the month for a specified date

GETDATE()-  Returns the current database system date and time

GETUTCDATE()- Returns the current database system UTC date and time

ISDATE()- Checks an expression and returns 1 if it is a valid date, otherwise 0

MONTH()- Returns the month part for a specified date (a number from 1 to 12)

SYSDATETIME()- Returns the date and time of the SQL Server

YEAR()- Returns the year part for a specified date

SQL Server Advanced Functions

CAST()- Converts a value (of any type) into a specified datatype

COALESCE()- Returns the first non-null value in a list

CONVERT()- Converts a value (of any type) into a specified datatype

CURRENT_USER()- Returns the name of the current user in the SQL Server database

IIF()-   Returns a value if a condition is TRUE, or another value if a condition is FALSE

ISNULL()- Return a specified value if the expression is NULL, otherwise return the expression

ISNUMERIC()- Tests whether an expression is numeric

NULLIF()- Returns NULL if two expressions are equal

SESSION_USER()- Returns the name of the current user in the SQL Server database

SESSIONPROPERTY()- Returns the session settings for a specified option

SYSTEM_USER()- Returns the login name for the current user

USER_NAME()- Returns the database username based on the specified id