Collation Types
Collation can be specified as a keyword in the definition of a field/property or the definition of an index.
Collation can be specified by applying a collation function to a field name in a query clause. The % prefix is required when specifying a collation function.
Collation is in ascending ASCII/Unicode sequence, with the following transformations:
-
EXACT — Enforces case sensitivity for string data without adding or stripping characters. Collates canonical numbers in numeric order, then collates strings in character-by-character ASCII order. String collation is case sensitive. String collation includes non-canonical numbers (such as 088) and mixed numeric strings (such as 22 Elm Street). Because canonical numbers collate before strings, including single characters and non-canonical numeric strings, EXACT is generally not recommended for string data that may contain values in canonical numeric format (for example 123 or -.57).
-
SQLSTRING — Strips trailing whitespace (spaces, tabs, and so on), and adds one leading blank space to the beginning of the string. It collates any value containing only whitespace (spaces, tabs, and so on) as the SQL empty string. SQLSTRING supports an optional maxlen integer value.
-
SQLUPPER — Converts all alphabetic characters to uppercase, strips trailing whitespace (spaces, tabs, and so on), and then adds one leading space character to the beginning of the string. The reason this space character is prepended is to force numeric values to be collated as strings (because the space character is not a valid numeric character). This transformation also causes SQL to collate the SQL empty string ('') value and any value containing only whitespace (spaces, tabs, and so on) as a single space character. SQLUPPER supports an optional maxlen integer value. Note that the SQLUPPER transform is not the same as the result of the SQL function UPPER.
-
TRUNCATE — Enforces case sensitivity for string data and (unlike EXACT) allows you to specify a length at which to truncate the value. This is useful when indexing exact data that is longer than what is supported for use in a subscript. It takes a positive integer argument, in the form %TRUNCATE(string,n), to truncate the string to the first n characters, which improves indexing and sorting on long strings. If you do not specify a length for TRUNCATE, it behaves identically to EXACT; while this behavior is supported. your definitions and code may be easier to maintain if you use TRUNCATE only when you have a length defined and EXACT when you do not.
-
PLUS — Makes the value numeric. A non-numeric string value is returned as 0.
-
MINUS — Makes the value numeric and changes its sign. A non-numeric string value is returned as 0.
In an SQL query, you can specify a collation function without parentheses %SQLUPPER Name or with parentheses %SQLUPPER(Name). If the collation function specifies truncation, the parentheses are required %SQLUPPER(Name,10).
Three collation types: SQLSTRING, SQLUPPER, and TRUNCATE support an optional maxlen integer value. If specified, maxlen truncates parsing of the string to the first n characters. This can be used to improve performance when indexing and sorting long strings. You can use maxlen in a query to sort on, group by, or return a truncated string value.
You can also perform collation type conversions using the %SYSTEM.Util.Collation()Opens in a new tab method.
