Scalar Functions

Table of Contents

Overview
See also
Related

Scalar functions compute a value for each row. They can be used in field lists, WHERE clauses, ORDER BY and GROUP BY expressions, and in inserted and updated values. Function names are not case-sensitive and must be followed by parentheses, even without parameters: Guid().
Boolean functions return 1 for true and 0 for false. A null parameter generally makes the result null.

Abs - Returns the absolute value of a number.
Ceil - Returns the smallest whole number that is greater than or equal to the given number.
Checksum - Returns a simple 16-bit checksum (0 to 65535) of the value's ASCII bytes. It is fast but not collision resistant; use Sha256 when the value must be unique.
Coalesce - Returns the first of the given values that is not null. Accepts any number of values.
Concat - Concatenates all of the given values that are not null. Accepts any number of values. The + operator can also be used to concatenate, but a null on either side of + makes the whole result null.
DateAdd - Adds offset intervals to a date/time value and returns the new date/time. Use a negative offset to subtract. Interval names are not case-sensitive.
DateDiff - Returns the number of intervals between two date/time values (date2 minus date1). See DateAdd for the interval names.
DateTime - Returns the server's current local date and time, formatted with the optional .NET date/time format string.
DateTimeUTC - Returns the current UTC date and time, formatted with the optional .NET date/time format string. See DateTime for the format specifiers.
Floor - Returns the largest whole number that is less than or equal to the given number.
FormatDateTime - Parses a date/time value and returns it formatted with a .NET date/time format string. See DateTime for the format specifiers.
FormatNumeric - Formats a number using a .NET numeric format string and returns the result as text.
Guid - Returns a new random globally unique identifier.
IfNull - Returns the given value, or the default value when the given value is null.
IfNullNumeric - Returns the given number, or the default number when the given value is null.
IIF - Short for "immediate if": returns whenTrue when the condition is true and whenFalse otherwise. The condition is usually one of the boolean functions such as IsEqual or IsGreater.
IndexOf - Returns the zero-based position of the first occurrence of textToFind in textToSearch, starting the search at offset. Returns -1 when it is not found.
IsBetween - Returns true (1) if the value is within the given range, including the range boundaries.
IsDouble - Returns true (1) if the given value can be converted to a decimal number.
IsEmpty - Returns true (1) if the given value is null or an empty string.
IsEqual - Returns true (1) if the two given values are equal. The comparison is case-sensitive.
IsGreater - Returns true (1) if the first value is greater than the second.
IsGreaterOrEqual - Returns true (1) if the first value is greater than or equal to the second.
IsInteger - Returns true (1) if the given value is a whole number, with an optional leading sign.
IsLess - Returns true (1) if the first value is less than the second.
IsLessOrEqual - Returns true (1) if the first value is less than or equal to the second.
IsLike - Returns true (1) if the text matches the pattern. The pattern uses the same wildcards as Like: % matches any number of characters and _ matches a single character.
IsNotBetween - Returns true (1) if the value is outside of the given range. The range boundaries are considered inside the range.
IsNotEqual - Returns true (1) if the two given values are not equal. The comparison is case-sensitive.
IsNotLike - Returns true (1) if the text does not match the pattern. See Like for the wildcards.
IsNull - Returns true (1) if the given value is null, otherwise false (0). This is how to test for missing fields and for the unmatched side of an outer join.
IsNumeric - Returns true (1) if the given value is a number: digits with an optional leading sign and an optional decimal point. Null and empty values return 0.
IsString - Returns true (1) if the given value cannot be converted to a number.
LastIndexOf - Returns the zero-based position of the last occurrence of textToFind in textToSearch. Returns -1 when it is not found.
Left - Returns the given number of characters from the start of the value.
Length - Returns the number of characters in the given value.
NullIf - Returns null when the conditional is true, otherwise returns the value.
NullIfNumeric - Returns null when the conditional is true, otherwise returns the number.
NullWhen - Returns null when the value equals compareToValue, otherwise returns the value.
NullWhenNumeric - Returns null when the number equals compareToValue, otherwise returns the number.
Pow - Returns x raised to the power of y.
Right - Returns the given number of characters from the end of the value.
Round - Rounds a number to the given number of decimal places. Midpoint values are rounded to the nearest even number (2.5 rounds to 2).
Sha1 - Returns the SHA-1 hash of the value's UTF-8 bytes as a lower case hex string.
Sha256 - Returns the SHA-256 hash of the value's UTF-8 bytes as a lower case hex string.
Sha512 - Returns the SHA-512 hash of the value's UTF-8 bytes as a lower case hex string.
SubString - Returns length characters of the value, starting at the zero-based startIndex.
ToLower - Returns the value converted to lower case.
ToNumeric - Returns the value converted to a number.
ToProper - Returns the value with the first letter of each word capitalized (title case).
ToString - Returns the number as a string, so that + concatenates it instead of adding it.
ToUpper - Returns the value converted to upper case.
Trim - Removes white space from the start and end of the value. When characters is given, each of those characters is removed from the start and end instead.