InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Functions

InterSystems SQL Reference
Previous section           Next section
InterSystems: The power behind what matters   

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 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:
%EXACT is an InterSystems 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 DISTINCT 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.
FROM Sample.Person
WHERE %EXACT(Name) > 'Smith'
FROM Sample.Person
WHERE %EXACT Name > 'Smith'
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-22 04:57:16