InterSystems SQL Reference
A collation function that converts characters to the EXACT collation format.
||A string expression, which can be the name of a column, a string literal, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).
in the EXACT collation sequence. This is principally used to collate data values in case-sensitive order. The SQL default is to convert all letters to uppercase for the purpose if collation.
This collation sequence orders values as follows:
String values collate in case-sensitive string order. The EXACT collation sequence for strings is the same as the ANSI-standard ASCII collation sequence: digits are collated before uppercase alphabetic characters and uppercase alphabetic characters are collated before lowercase alphabetic characters. Punctuation characters occur at several places in the sequence.
A mixed-numeric string, such as 123 Elm Street is collated in case-sensitive string order. This means that 123 Elm sorts before 2 Elm.
Pure numeric values (values for which x=+x) collate in numeric order. For example, -19, -2, -1, 0, 1, 5, 10, 20, 100. This is the same as default collation.
NULL collates before all actual values. %EXACT
has no effect on NULLs. This is the same as default collation.
is an InterSystems SQL extension and is intended for SQL lookup queries.
collates an input string as either wholly numeric or as a mixed-character string in which numbers are treated the same as any other character. Compare this to %MVR
collation, which sorts a string based on the numeric substrings within the string.
DISTINCT and GROUP BY
clause and the GROUP BY
clause group values based on their uppercase default collation, and return values in all uppercase letters, even when none of the actual data values are in all uppercase letters.
By default, SQL indices represent string data in uppercase default collation. For this reason, specifying EXACT collation may prevent the use of an index with potentially significant performance implications.
The following examples uses %EXACT
to return all Name values that are higher in the collating sequence than 'Smith'. The first example uses parentheses syntax, the second omits the parentheses.
WHERE %EXACT(Name) > 'Smith'
WHERE %EXACT Name > 'Smith'
Content Date/Time: 2019-07-18 06:47:30