Scalar Functions

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.

• ShowScalarFunctions
• Expressions