%EXACT (SQL)

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

Synopsis

%EXACT(expression)

%EXACT expression

Description

%EXACT returns expression in the EXACT collation sequence. This collation sequence orders values as follows:

NULL collates before all actual values. %EXACT has no effect on NULLs. This is the same as default collation.
Canonical numeric values (whether input as a number or as a string) collate in numeric order before string values.
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.

This results in a sequence such as the following:

NULL
-2        /* canonical number collation */
0
1
2
10
22
88
''         /* empty string */
#          /* character-by-character string collation */
-00        /* non-canonical number collates as string */
0 Elm St.  /* character-by-character string collation */
022        /* non-canonical number collates as string */
1 Elm St.  
19 Elm St.
19 elm St. /* string collation is case-sensitive */
19Elm St.
2 Elm St.
201 Elm St.
21 Elm St.
Elm St.

%EXACT is commonly used to collate string values containing letters in case-sensitive order. The SQL default is to convert all letters to uppercase for the purpose of collation.

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

%EXACT collates an input string as either wholly numeric (canonical) 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

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.

You can use %EXACT to group values by case-sensitive values: SELECT Name FROM mytable GROUP BY %EXACT(Name)
You can use %EXACT to return an actual case-sensitive value for each group: SELECT %EXACT(Name) FROM mytable GROUP BY Name

Note:

By default, SQL indexes 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.

Arguments

commitmode

A string expression, which can be the name of a column, a string literal, a numeric, or the result of another function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).

Examples

The following example orders all street addresses by %EXACT collation:

SELECT Name,Street 
FROM Sample.Person
ORDER BY %EXACT Street

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.

SELECT Name 
FROM Sample.Person
WHERE %EXACT(Name) > 'Smith'

SELECT Name 
FROM Sample.Person
WHERE %EXACT Name > 'Smith'