Symbols Used in InterSystems SQL

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.

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.

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.

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

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

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.

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

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.

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.

Equal sign (61): Equal to comparison condition.

In WHERE clause, an Inner Join.

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.

The letter “E” (69, 101): Exponent indicator.

%PATTERN code specifying any printable character.

Backslash (92): Integer division arithmetic operator.

In %MATCHES pattern string an escape character.

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.