Skip to main content

Symbols Used in Caché SQL

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

Quotes (34): Encloses a delimited identifier name.

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.

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

Embedded SQL invocation prefix: &sql(SQL commands).

' Single quote character (39): Encloses a string literal.
''

Double single quote characters: An empty 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 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 /*.
*= 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.

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: tablename.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 DDLImport() 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.

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

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

FeedbackOpens in a new tab