Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  Symbols and Syntax Conventions  /  Symbols Used in InterSystems SQL

InterSystems SQL Reference
Symbols Used in InterSystems SQL
InterSystems: The power behind what matters   

A table of characters used in InterSystems SQL as operators, etc.
Table of Symbols
The following are the literal symbols used in InterSystems SQL on the InterSystems IRIS Data Platform™. (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 InterSystems IRIS 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 InterSystems 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: tablealias.fieldname
Decimal point for numeric literals in American numeric format.
Date delimiter for Russian, Ukrainian, and Czech locales: DD.MM.YYYY
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
A time delimiter for hours, minutes, and seconds. In CAST and CONVERT functions, an optional thousandth-of-a-second delimiter.
In trigger code a prefix indicating an 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, InterSystems 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 InterSystems IRIS 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.