SQL Server Functions

SQL Server String Functions

SQL Server provides a comprehensive range of built-in functions encompassing string manipulation, numeric operations, date handling, conversion tasks, and advanced functionalities.

Function	Description
ASCII	“Retrieve the ASCII value of a specific character.”
CHAR	“Retrieve the character corresponding to a given ASCII code.”
CHARINDEX	“Determines the index of a substring within a string.”
CONCAT	Concatenates two or more strings together.
Concat with +	Concatenates multiple strings into one.
CONCAT WS	Concatenates multiple strings with a specified separator.
DATALENGTH	Returns the size in bytes required to represent an expression.
DIFFERENCE	Compares two SOUNDEX values and returns an integer result.
FORMAT	Formats a value according to a specified format.
LEFT	Retrieves a specified number of characters from the beginning of a string.
LEN	Returns the number of characters in a string.
LOWER	Converts a string to lowercase letters.
LTRIM	Trims leading spaces from a string.
NCHAR	Returns the Unicode character corresponding to a given numeric code.
PATINDEX	Returns the index or position of a specified pattern within a string.
QUOTENAME	Returns a Unicode string formatted with delimiters to comply with SQL Server’s rules for delimited identifiers.
REPLACE	Substitutes every instance of a substring within a string with a different substring.
REPLICATE	Duplicates a string a specified number of times
REVERSE	Returns the reversed version of a string.
RIGHT	Extracts a specified number of characters from the end of a string.
RTRIM	Trims trailing spaces from a string.
SOUNDEX	Returns a four-character code that assesses the similarity between two strings.
SPACE	Returns a string consisting of a specified number of space characters.
STR	Converts a number to its string representation.
STUFF	Removes a segment of a string and inserts another segment into the string, beginning at a specified position.
SUBSTRIN	Retrieves a subset of characters from a string.
TRANSLATE	Returns a string where characters specified in the second argument are replaced with corresponding characters from the third argument in the first argument string.
TRIM	Trims leading and trailing spaces or other specified characters from a string.
UNICODE	Returns the Unicode code point for the first character of the input expression.
UPPER	Changes a string to uppercase.

SQL Server Math/Numeric Functions

Function	Description
ABS	Give me the absolute value of a number.
ACOS	Returns the inverse cosine of a number
ASIN	Give me the inverse sine of a number.
ATAN	Give me the inverse tangent of a number.
ATN2	Returns the inverse tangent of the ratio of two numbers.
AVG	Returns the mean value of an expression.
CEILING	Returns the smallest integer that is greater than or equal to a given number
COUNT	Returns the count of records retrieved by a select query.
COS	Returns the cosine value of an angle or number.
COT	Returns the cotangent value of an angle or number.
DEGREES	Converts an angle from radians to degrees.
EXP	Returns the exponential function of a specified number, where the base of the natural logarithm (e ≈ 2.718) is raised to that number’s power.
FLOOR	Returns the largest integer that is less than or equal to a given number
LOG	Returns either the natural logarithm of a number or the logarithm of a number to a specified base, depending on the context or function used.
LOG10	Returns the logarithm of a number to base 10, often referred to as the common logarithm.
MAX	Returns the largest value among a given set of values.
MIN	Returns the smallest value among a given set of values.
PI	Returns the mathematical constant representing the ratio of a circle’s circumference to its diameter.
POWER	Returns the result of raising a number to a specified power.
RADIANS	Converts a degree measurement into its equivalent value in radians.
RAND	Generates a random number
ROUND	Adjusts a number to a specified precision in decimal places
SIGN	Returns the signum function of a number
SIN	Returns the sine value of an angle or number
SQRT	Returns the non-negative square root of a number
SQUARE	Returns the result of multiplying a number by itself
SUM	Returns the total of a set of values
TAN	Returns the tangent value of an angle or number

SQL Server Date Functions

Function	Description
CURRENT TIMESTAMP	Returns the current date and time
DATEADD	Take a date and add a specified time or date interval to it, then provide the resulting date.
DATEDIFF	Calculates and returns the duration or interval between two dates.
DATEFROMPARTS	Constructs and returns a date using provided values for year, month, and day.
DATENAME	Returns a specific component of a date as a string.
DATEPART	Returns a specific component of a date as an integer value.
DAY	Returns the numerical day of the month for a given date.
GETDATE	Retrieves the current date and time from the database system.
GETUTCDATE	Retrieves the current UTC date and time from the database system.
ISDATE	Validates an expression to determine if it represents a valid date; returns 1 for true (valid date) and 0 for false (invalid date).
MONTH	Returns the month component of a specified date as a number between 1 and 12.
SYSDATETIME	Returns the month of a given date as a number ranging from 1 to 12.
YEAR	Returns the current date and time from SQL Server.

SQL Server Advanced Functions

Function	Description
CAST	Transforms a value into a designated data type.
COALESCE	Provides the initial non-null value found within a list.
CONVERT	Changes the data type of a given value to a specified type.
CURRENT USER	Retrieves the username associated with the current session in the SQL Server database.
IIF	Returns one value if a condition is met, and another value if it is not.
ISNULL	Return a specific value when an expression evaluates to NULL, otherwise return the evaluated expression itself.
ISNUMERIC	Checks if an expression qualifies as numeric.
NULLIF	Returns NULL if two expressions are identical.
SESSION USER	Retrieves the username associated with the current session in the SQL Server database.
SESSIONPROPERTY	Retrieves the current session’s configuration or settings for a specified option.
SYSTEM USER	Retrieves the login name of the current user in the context of a database system.
USER NAME	Returns the database user name corresponding to a specified user ID.