Skip to main content


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



%EXACT expression


Argument Description
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()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab 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

FeedbackOpens in a new tab