Caché SQL Reference
%SQLUPPER
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A collation function that sorts values as uppercase strings.
Synopsis
%SQLUPPER(expression[,maxlen]) 
%SQLUPPER expression
Arguments
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).
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 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. Leading and trailing zeros are removed from numbers. %SQLUPPER is the Caché default collation.
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 Caché ObjectScript using the Collation() method of the %SYSTEM.Util class:
  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",7)
 
This function can also be invoked from Caché ObjectScript using the SQLUPPER() 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:
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
 
See Also