%SQLUPPER

A collation function that sorts values as uppercase strings.

Synopsis

%SQLUPPER(expression[,maxlen]) 
%SQLUPPER expression

Arguments

Argument	Description
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 VARCHAR2). 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)).

Description

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 long 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 255-character limit for Caché subscripts. When converting from non-Caché systems, some users encountered problems when they indexed on a VARCHAR(255) field and then tried to insert data into the table. 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.

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.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.

Note:

To change the system-wide default collation from %SQLUPPER (which is not case-sensitive) to %SQLSTRING (which is case-sensitive), use the following command:

  WRITE $$SetEnvironment^%apiOBJ("collation","%Library.String","SQLSTRING")

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:

%ALPHAUP: converts letters to uppercase, has no effect on number characters, deletes all punctuation characters except commas and question marks (including deleting the minus sign and a period used as a decimal separator from numbers), deletes all blank spaces (leading, trailing, and embedded). Does not force numerics to be interpreted as a string.
UPPER, %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.
%STRING: converts letters to uppercase, deletes all punctuation except commas (including deleting the minus sign and a period used as a decimal separator from numbers), deletes all blank spaces (leading, trailing, and embedded). 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.
%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:

%ALPHAUP and %STRING	%SQLUPPER, %SQLSTRING, and all other case conversion functions
5988 Clinton 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, 6643 First Street, 665 Ash Drive, 66 Main Street, 672 Main Court, 6754 Oak Court, 6986 Madison Blvd, 6 Oak Avenue, 7000 Ash Court, 709 Oak Avenue	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, 6643 First Street, 665 Ash Drive, 672 Main Court, 6754 Oak Court, 6986 Madison Blvd, 7000 Ash Court, 709 Oak Avenue

Examples

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