%SQLUPPER(expression[,maxlen]) %SQLUPPER 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 VARCHAR). expression can be a subquery.|
|maxlen||Optional — An integer, which specifies that the collated value will be truncated to the value of maxlen. Note that maxlen includes the appended leading blank space. You can enclose maxlen with double parentheses to suppress literal substitution: ((maxlen)).|
SQLUPPER is the default collation.
%SQLUPPER converts expression to a format that is sorted as a (not case-sensitive) uppercase string. %SQLUPPER converts all alphabetic characters to uppercase, strips trailing whitespace (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. This appended blank space causes NULL and numeric values to be collated as strings.
SQL converts numeric values to canonical form (removing leading and trailing zeros, expanding exponents, etc.) before passing the number to the function. SQL does not convert numeric strings to canonical form.
Because %SQLUPPER appends a blank space to all values, it collates a NULL value as a blank space, with a string length of 1. %SQLUPPER collates any value containing only whitespace (spaces, tabs, and so on) as the SQL empty string (''). When %SQLUPPER appends a blank space to an empty (zero-length) string, it collates as a blank space plus the internal representation of an empty string, $CHAR(0), resulting in a string length of 2.
The optional maxlen argument truncates the converted expression string to the specified number of characters when indexing or collating. For example, if you insert a string with maxlen truncation, the full string is inserted and can be retrieved by a SELECT statement; the index global for this string is truncated to the specified length. This means that ORDER BY and comparison operations only evaluate the truncated index string. Such truncation is especially useful for indexing on strings that exceed the maximum character length for InterSystems IRIS subscripts. With the maxlen argument, if you need to index on a long field, you can use the truncation length parameter.
%SQLUPPER performs maxlen truncation after converting expression; if maxlen exceeds the length of the converted expression no padding is added. The maximum string length is 3,641,144 characters; no maximum is enforced for maxlen.
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:
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",7)
This function can also be invoked from ObjectScript using the SQLUPPER()Opens in a new tab method call:
WRITE $SYSTEM.SQL.Functions.SQLUPPER("The quick, BROWN fox.")
Both of these methods support truncation after SQLUPPER conversion. Note that the truncation length must include the appended blank:
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",7,6),! WRITE $SYSTEM.SQL.SQLUPPER("The quick, BROWN fox.",6)
For a case-sensitive string conversion, refer to %SQLSTRING.
To change the system-wide default collation from %SQLUPPER (which is not case-sensitive) to %SQLSTRING (which is case-sensitive), use the following command:
After issuing this command, you must purge indexes, recompile all classes, then rebuild indexes. Do not rebuild indices while the table’s data is being accessed by other users. Doing so may result in inaccurate query results.
Other Case Conversion Functions
The %SQLUPPER function is the preferred way in SQL to convert a data value for not case-sensitive comparison or collation. %SQLUPPER adds a leading blank space to the beginning of the data, which forces numeric data and the NULL value to be interpreted as strings.
The following are other functions for converting the case of a data value:
UPPER and UCASE: converts letters to uppercase, has no effect on number characters, punctuation characters, embedded spaces, and leading and trailing blank spaces. Does not force numerics to be interpreted as a string.
LOWER and LCASE: converts letters to lowercase, has no effect on number characters, punctuation characters, embedded spaces, and leading and trailing blank spaces. Does not force numerics to be interpreted as a string.
%SQLSTRING: does not convert letter case. However, it adds a leading blank space to the beginning of the data, which forces numeric data and the NULL value to be interpreted as strings.
Alphanumeric Collation Order
The case conversion functions collate data values that begin with a number using different algorithms, as follows:
|%MVR||%SQLUPPER, %SQLSTRING, and all other case conversion functions|
|6 Oak Avenue, 66 Main Street, 66 Oak Street, 641 First Place, 665 Ash Drive, 672 Main Court, 709 Oak Avenue, 5988 Clinton Avenue, 6023 Washington Court, 6090 Elm Court, 6185 Clinton Drive, 6209 Clinton Street, 6284 Oak Drive, 6310 Franklin Street, 6406 Maple Place, 6572 First Avenue, 6643 First Street, 6754 Oak Court, 6986 Madison Blvd, 7000 Ash Court,||5988 Clinton Avenue, 6 Oak Avenue, 6023 Washington Court, 6090 Elm Court, 6185 Clinton Drive, 6209 Clinton Street, 6284 Oak Drive, 6310 Franklin Street, 6406 Maple Place, 641 First Place, 6572 First Avenue, 66 Main Street, 66 Oak Street, 6643 First Street, 665 Ash Drive, 672 Main Court, 6754 Oak Court, 6986 Madison Blvd, 7000 Ash Court, 709 Oak Avenue,|
The following query uses %SQLUPPER with a string truncation to return the first two characters of each name in uppercase. Note that the string truncation is 3 (not 2) because of the leading blank added by %SQLUPPER. The ORDER BY clause uses this two-character field to put the rows in a rough collation sequence:
SELECT Name, %SQLUPPER(Name,3) AS FirstTwo FROM Sample.Person ORDER BY FirstTwo
The following example applies %SQLUPPER to a subquery:
SELECT TOP 5 Name, %SQLUPPER((SELECT Name FROM Sample.Company),10) AS Company FROM Sample.Person
%SQLSTRING collation function
%TRUNCATE collation function
Collation chapter in Using InterSystems SQL