Caché SQL Reference
Symbols Used in Caché SQL
[Home]  [Next]
InterSystems: The power behind what matters   
Class Reference   

A table of characters used in Caché SQL as operators, etc.
Table of Symbols
The following are the literal symbols used in Caché SQL. (This list does not include symbols indicating format conventions, which are not part of the language.) There is a separate table for symbols used in ObjectScript.
The name of each symbol is followed by its ASCII decimal code value.
Symbol Name and Usage
[space] or [tab] White space (Tab (9) or Space (32)): One or more whitespace characters between keywords, identifiers, and variables.
! Exclamation mark (33): OR logical operator in between predicates in condition expressions. Used in the WHERE clause, the HAVING clause, and elsewhere.
!= Exclamation mark/Equal sign: Is not equal to comparison condition.
Encloses a string literal (not recommended, use single quotes instead).
In Dynamic SQL used to enclose literal values for class method arguments, such as SQL code as a string argument for the %Prepare() method, or input parameters as string arguments for the %Execute() method.
In %PATTERN used to enclose a literal value within a pattern string. For example, '3L1"L".L' (meaning 3 lowercase letters, followed by the capital letter “L”, followed by any number of lowercase letters).
In XMLELEMENT used to enclose a tag name string literal.
"" Double quotes: A literal quotes character within a delimited identifier.
Pound sign (35): Valid identifier name character (not first character).
With spaces before and after, modulo arithmetic operator.
For Embedded SQL, ObjectScript macro preprocessor directive prefix. For example, #Include.
Dollar sign (36): Valid identifier name character (not first character).
First character of some Caché extension SQL functions.
$$ Double dollar sign: used to call a ObjectScript user-defined function (also known as an extrinsic function).
Percent sign (37): Valid first character for identifier names (first character only).
First character of some Caché SQL extensions to the SQL standard, including string collation functions (%SQLUPPER), aggregate functions (%DLIST), and predicate conditions (%STARTSWITH).
First character of %ID, %TABLENAME, and %CLASSNAME keywords in SELECT.
First character of some privilege keywords (%CREATE_TABLE, %ALTER) and some role names (%All).
First character of some Embedded SQL system variables (%ROWCOUNT, %msg).
Data type max length indicator: CHAR(%24)
LIKE condition predicate multi-character wildcard.
Double percent sign: Prefix for the pseudo-field reference variable keywords: %%CLASSNAME, %%CLASSNAMEQ, %%ID, and %%TABLENAME, used in ObjectScript computed field code and trigger code.
Ampersand (38): AND logical operator in WHERE clause and other condition expressions.
$BITLOGIC bitstring And operator.
' Single quote character (39): Encloses a string literal.
A literal single quote character within a string value. For example: 'can''t'
( )
Parentheses (40,41): Encloses comma-separated lists. Encloses argument(s) of an SQL function. Encloses the parameter list for a procedure, method, or query. In most cases, the parentheses must be specified, even if no arguments or parameters are supplied.
In a SELECT DISTINCT BY clause, encloses an item or comma-separated list of items used to select unique values.
In a SELECT statement, encloses a subquery in the FROM clause. Encloses the name of a predefined query used in a UNION.
Encloses embedded SQL code: &sql( code )
Used to enforce precedence in arithmetic operations: 3+(3*5)=18. Used to group predicates: WHERE NOT (Age<20 AND Age>12).
(( )) Double Parentheses: suppress literal substitution in cached queries. For example, SELECT TOP ((4)) Name FROM Sample.Person WHERE Name %STARTSWITH (('A')). Optimizes WHERE clause selection of a non-null outlier value.
Asterisk (42): A wildcard, indicating “all” in the following cases: In SELECT retrieve all columns: SELECT * FROM table. In COUNT, count all rows (including nulls and duplicates). In GRANT and REVOKE, all basic privileges, all tables, or all currently defined users.
In %MATCHES pattern string a multi-character wildcard.
Multiplication arithmetic operator.
*/ Asterisk slash: Multi-line comment ending indicator. Comment begins with /*.
*= Asterisk, equal sign: In WHERE clause, a Right Outer Join.
+ Plus sign (43): Addition arithmetic operator. Unary positive sign operator.
Comma (44): List separator, for example, multiple field names.
In data size definition: NUMERIC (precision,scale).
Hyphen (minus sign) (45): Subtraction arithmetic operator. Unary negative sign operator.
SQLCODE error code prefix: –304.
In %MATCHES pattern string a range indicator specified within square brackets. For example, [a-m].
–– Double hyphen: Single-line comment indicator.
–> Hyphen, greater than (arrow): implicit join arrow syntax.
Period (46): Used to separate parts of multipart names, such as qualified table names: schema.tablename, or column names: tablename.fieldname
Decimal point in numeric literals.
Prefixed to a variable or array name, specifies passing by reference: .name
%PATTERN pattern string multi-character wildcard.
/* Slash asterisk: Multi-line comment begins indicator. Comment ends with */.
Colon (58): Host variable indicator prefix: :var
In trigger code a prefix indicating a ObjectScript label line.
In CREATE PROCEDURE ObjectScript code body, a macro preprocessor directive prefix. For example, :#Include.
:: Double colon: In trigger code this doubled prefix indicates that the identifier (::name) beginning that line is a host variable, not a label line.
; Semicolon (59): SQL end of statement delimiter in procedures, methods, queries, and trigger code. Accepted as an optional end of statement delimiter by DDLImport(). Otherwise, Caché SQL does not use or allow a semicolon at the end of an SQL statement.
< Less than (60): Less than comparison condition.
<= Less than or equal to: Less than or equal to comparison condition.
<> Less than/Greater than: Is not equal to comparison condition.
In WHERE clause, an Inner Join.
=* Equal sign, asterisk: In WHERE clause, a Left Outer Join.
> Greater than (62): Greater than comparison condition.
>= Greater than or equal to: Greater than or equal to comparison condition.
Question mark (63): In Dynamic SQL, an input parameter variable supplied by the Execute method.
In %MATCHES pattern string a single-character wildcard.
@ At sign (64): Valid identifier name character (not first character).
E, e
%PATTERN code specifying any printable character.
[ Open square bracket (91): Contains predicate. Used in the WHERE clause, the HAVING clause, and elsewhere.
[ ] Open and close square brackets: In %MATCHES pattern string, encloses a list or range of match characters. For example, [abc] or [a-m].
In %MATCHES pattern string an escape character.
] Close square bracket (93): Follows predicate. Used in the WHERE clause, the HAVING clause, and elsewhere.
^ Caret (94): In %MATCHES pattern string a NOT character. For example, [^abc].
Underscore (95): Valid first (or subsequent) character for identifier names. Valid first character for certain user names (but not passwords).
Used in multipart column names to represent embedded serial class data: SELECT Home_State, where Home is a serial container field.
LIKE condition predicate single-character wildcard.
{ }
Curly braces (123,125): Enclose ODBC scalar functions: {fn name(...)}. Enclose time and date construct functions: {d 'string'}, {t 'string'}, {ts 'string'}.
Enclose ObjectScript code in procedures, methods, queries, and trigger code.
Compound ID indicator. Used by Caché as a delimiter between multiple properties in a generated compound object ID (a concatenated ID). This can be either an IDKey index defined on multiple properties (prop1||prop2), or an ID for a parent/child relationship (parent||child). Cannot be used in IDKEY field data.