Skip to main content

Symbols Used in InterSystems SQL

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 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.

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.

FeedbackOpens in a new tab