SQL Constants and Literals

This topic describes constants that are built into the Manifold query engine as well as notation used for literals.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands, functions, and constants.

Constants

Some useful constants:

 

CR

A string constant for a carriage return character.

CRLF

A string constant consisting of CR plus LF, the end of line sequence in Windows.

E

A number, 2.718...

FALSE

A boolean.

LF

A string constant for a line feed character.

NULL

NULL

PI

A number, 3.1415...

TRUE

A boolean.

WHITESPACE

A string constant for use with StringTrimXxx functions:  contains a space character, a tab character, CR and LF.

Literals

Enter literals using the following notation.  Replace the ... in the pattern with the desired literal.

 

'...'

Enclose text literals in single quote ' characters:  SELECT * FROM [Orders] WHERE [City] = 'London' ;

\

The backslash \ character escapes the following character.  To enter the literal text don't we would use 'don\'t' in the query.  To enter the backslash character itself use two backslashes: \\

@'...'

A Manifold innovation that allows specifying string literals in expressions without using escape characters like \ in SQL.  Replace the three dots in the pattern at left with the string literal desired.  This notation applies in SQL expressions and  regular expressions, where it is especially useful.   The string literal cannot include a single ' quote.  To include a single quote within an expression that also uses this @ notation, we must build it with a concatenation expression in the form @'...' & '\'' & @'...'     See the Regular Expressions topic for examples.

#...#

Enclose date literals in hash # characters:  SELECT * FROM [Orders] WHERE [Date] = #6/24/2016# ;

AM or PM

Date literals including time can use AM or PM.

(date variations)

Surprisingly many common ways of specifying dates will work as literals, including

#5/15/1991#,  #May 15 1991#,  #May 15, 1991#,  #15 May 1991#,  #15 May, 1991#, and  #1991 May 15#.

 

We can write SELECT * FROM [Orders] WHERE [Order Date] = #15 May 1991#;

TRUE or true

Boolean literals may be either true or false  with case not significant:  SELECT * FROM [Orders] WHERE [Shipped] = TRUE;  and SELECT * FROM [Orders] WHERE [Shipped] = trUe; are equivalent.   We do not need the = comparison since SELECT * FROM [Orders] WHERE [Shipped] ; is the same.

 

 

Notes

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

SQL Operators

 

SQL Statements

 

SQL Functions

 

Temporary Databases

 

EXECUTE