Symbols Used in Caché SQL

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.
$	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 shell 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 in numeric literals. Date delimiter. 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. In trigger code a prefix indicating a 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, Caché 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.
@	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 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.
\|\|	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.