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.
In SQL Shell, the ! command is used to issue an ObjectScript command line. |
!= |
Exclamation mark/Equal sign: Is not equal to comparison condition. |
" |
Quotes (34): Encloses a delimited identifier name.
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. |
"" |
Two quotes: By themselves, an invalid delimited identifier. Within a delimited identifier, an escape sequence for a literal quote character. For example, "a""good""id". |
# |
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.
In SQL Shell the # command is used to recall statements from the SQL Shell history buffer. |
$ |
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 an ObjectScript user-defined function (also known as an extrinsic function). For more details, see Function and Method Call Selection in the selectItem argument of the SELECT reference page. |
% |
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.
Embedded SQL invocation prefix: &sql(SQL commands). |
' |
Single quote character (39): Encloses a string literal. |
'' |
Double single quote characters: An empty string literal.
An escape sequence for 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 host variable array subscripts. For example, INTO :var(1),:var(2)
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 /*. |
+ |
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.
Date delimiter.
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 (47): Division arithmetic operator.
Date delimiter. |
/* |
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 ImportDDL() or wherever specifying SQL code using a TSQL dialect. 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. |
= |
Equal sign (61): Equal to comparison condition.
In WHERE clause, an Inner 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.
In SQL Shell the ? command displays help text for SQL Shell commands. |
@ |
At sign (64): Valid identifier name character (not first character). |
E, e |
The letter “E” (69, 101): Exponent indicator.
%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]. |
\ |
Backslash (92): Integer division arithmetic operator.
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 column names to represent embedded serial class data: SELECT Home_State, where Home is a field that references a serial class and State is a property defined in that serial class.
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. |
|| |
Double vertical bar (124): Concatenation operator.
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. |