Expressions

Table of Contents



Expressions can be used in the field list of a Select, in Insert values, in Update assignments and in Where clauses.

  • Numbers: 10, -6, 3.14159
  • Strings: 'single quoted' or "double quoted". A doubled quote ('It''s') is not supported. A quote that follows a backslash does not end the string, but the backslash is kept: 'It's' returns It's.
  • Null: null


Operator Meaning
+ Adds numbers. When either side is a string, the values are concatenated: 'Id: ' + 5 returns 'Id: 5'.
- * / Subtraction, multiplication and division.
% Remainder (modulo).
Bitwise exclusive-or. Use Pow for exponentiation.
( ) Grouping, with standard order of operations.

A null on either side of + makes the result null (null propagation). Concat skips nulls instead.

These are used in Where clauses and join ON conditions:
Operator Meaning
= Equal (strings are compared without regard to case).
!= Not equal. The <> form is not supported.
> >= < <= Numeric comparisons.
LIKE, NOT LIKE Pattern matching, see Like.
BETWEEN, NOT BETWEEN Range matching, see Between.

Comparison operators can only be used in WHERE and ON clauses. Elsewhere, use the boolean functions such as IsEqual, IsGreater and IsLike, which return 1 or 0.

-- A line comment.
SELECT Id, Name FROM WordList:Language -- A trailing comment.
/* A block
   comment. */


Use Declare to define a variable for the rest of the script. Variables are referenced with an @ prefix. Applications using the client API pass parameters the same way, see Client.
DECLARE @Language = 2
SELECT Text FROM WordList:Word WHERE LanguageId = @Language


Every SELECT needs a FROM clause. To evaluate expressions that don't read any documents, select from the built-in Single schema, which contains exactly one document.
SELECT 10 + 10 as Twenty, 'Hello' + ' ' + 'World' as Greeting, Guid() as Id FROM Single




SQL :: IsInteger
Returns true (1) if the given value is a whole number, with an optional leading sign.
SQL :: IsLess
Returns true (1) if the first value is less than the second.
SQL :: IsLessOrEqual
Returns true (1) if the first value is less than or equal to the second.
SQL :: 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.
SQL :: IsNotBetween
Returns true (1) if the value is outside of the given range. The range boundaries are considered inside the range.
SQL :: IsNotEqual
Returns true (1) if the two given values are not equal. The comparison is case-sensitive.
SQL :: IsNotLike
Returns true (1) if the text does not match the pattern. See [[Like]] for the wildcards.
SQL :: 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.
SQL :: 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.
SQL :: IsString
Returns true (1) if the given value cannot be converted to a number.