Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

A collation function that converts characters to the EXACT collation format.

%EXACT expression
expression 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).
%EXACT returns expression in the EXACT collation sequence. This collation sequence orders pure numeric values (values for which x=+x) in numeric order first, followed by all other characters in 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.
%EXACT passes through NULLs unchanged.
%EXACT is a Caché SQL extension and is intended for SQL lookup queries.
You can perform the same collation conversion in ObjectScript using the Collation() method of the %SYSTEM.Util class.
%EXACT 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.
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.
FROM Sample.Person
WHERE Name > 'Smith'
FROM Sample.Person
WHERE %EXACT(Name) > 'Smith'
FROM Sample.Person
WHERE %EXACT Name > 'Smith'
See Also