Next section

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. Not recommended for use if your string data contains 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 appended 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.
Note:
There are also various legacy collation types, the use of which is not recommended.
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() method.
Next section