Query Collation

Letter Case: By default, a query displays strings with uppercase and lowercase letters. The exceptions to this are the DISTINCT or GROUP BY operations on a field of collation type SQLUPPER. These operations display that field in all uppercase letters. You can use the %EXACT collation function to reverse this letter case transformation and display the field in uppercase and lowercase letters. You should not use an %SQLUPPER collation function in the select-item list to display a field in all uppercase letters. This is because %SQLUPPER adds a space character to the length of the string. Use the UPPER function instead:

SELECT TOP 5 Name,$LENGTH(Name) AS NLen,
             %SQLUPPER(Name) AS UpCollN,$LENGTH(%SQLUPPER(Name)) AS UpCollLen,
             UPPER(Name) AS UpN,$LENGTH(UPPER(Name)) AS UpLen
FROM Sample.Person

String Truncation: You can use the %TRUNCATE collation function to limit the length of the string data you wish to display. %TRUNCATE is preferable to %SQLUPPER, which adds a space character to the length of the string.

SELECT TOP 5 Name,$LENGTH(Name) AS NLen,
             %TRUNCATE(Name,8) AS TruncN,$LENGTH(%TRUNCATE(Name,8)) AS TruncLen
FROM Sample.Person

Note that you cannot nest collation functions or case-transformation functions.

WHERE clause comparisons: Most WHERE clause predicate condition comparisons use the collation type of the field/property. Because string fields default to SQLUPPER, these comparisons are commonly not case-sensitive. You can use the %EXACT collation function to make them case-sensitive:

The following example returns Home_City string matches regardless of letter case:

SELECT Home_City FROM Sample.Person WHERE Home_City = 'albany'

The following example returns Home_City string matches that are case-sensitive:

SELECT Home_City FROM Sample.Person WHERE %EXACT(Home_City) = 'albany'

The SQL Follows operator ( ] ) uses the field/property collation type.

However, the SQL Contains operator ( [ ) uses EXACT collation, regardless of the collation type of the field/property:

SELECT Home_City FROM Sample.Person WHERE Home_City [ 'c' 
ORDER BY Home_City

The %MATCHES and %PATTERN predicate conditions use EXACT collation, regardless of the collation type of the field/property. The %PATTERN predicate provides both case-sensitive wildcards and a wildcard (‘A’) which is not case-sensitive.

ORDER BY clause: The ORDER BY clause uses the namespace default collation to order string values. Therefore, ORDER BY does not order based on lettercase. You can use %EXACT collation to order strings based on lettercase.

DISTINCT: The DISTINCT keyword uses the namespace default collation to eliminate duplicate values. Therefore, DISTINCT Name returns values in all uppercase letters. You can use EXACT collation to return values in mixed uppercase and lowercase. DISTINCT eliminates duplicates that differ only in letter case. To preserve duplicates that differ in case, but eliminate exact duplicates, use EXACT collation. The following example eliminates exact duplicates (but not lettercase variants) and returns all values in mixed uppercase and lowercase:

SELECT DISTINCT %EXACT(Name) FROM Sample.Person

A UNION involves an implicit DISTINCT operation.

GROUP BY: The GROUP BY clause uses the namespace default collation to eliminate duplicate values. Therefore, GROUP BY Name returns values in all uppercase letters. You can use EXACT collation to return values in mixed uppercase and lowercase. GROUP BY eliminates duplicates that differ only in letter case. To preserve duplicates that differ in case, but eliminate exact duplicates, you must specify the %EXACT collation function on the GROUP BY clause, not the select-item.

The following example returns values in mixed uppercase and lowercase; the GROUP BY eliminates duplicates, including those that differ in lettercase:

SELECT %EXACT(Name) FROM Sample.Person GROUP BY Name

The following example returns values in mixed uppercase and lowercase; the GROUP BY eliminates exact duplicates (but not lettercase variants):

SELECT Name FROM Sample.Person GROUP BY %EXACT(Name)