%STRING(expression[,maxlen]) %STRING 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 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)).|
%STRING converts expression to the STRING format:
Converts all letters to uppercase.
Removes all punctuation characters, except the comma. (%ALPHAUP removes all punctuation characters, except the comma and the question mark.)
Removes all blank spaces (leading, trailing, and embedded).
Then adds a single space to the beginning of the value, forcing numeric data and the NULL value to be collated as strings.
SQL converts a number to canonical form before passing the number to the function. %STRING then removes the period (used as the decimal separator character in many locales) and the minus sign, as well as other punctuation and blanks. For this reason, %STRING must be used with caution on any expression containing non-alphabetic information.
Because %STRING appends a blank space to all values, it collates a NULL value as a blank space, with a string length of 1. %STRING collates any value containing only whitespace (spaces, tabs, and so on) as the SQL empty string (''). When %STRING 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 truncation argument 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 STRING collation with a truncation length parameter. %STRING performs maxlen truncation after converting expression; if maxlen exceeds the length of the converted expression no padding is added.
%STRING is a Caché SQL extension and is intended for SQL lookup queries.
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",9)
This function can also be invoked from ObjectScript using the STRING() method call:
WRITE $SYSTEM.SQL.STRING("The quick, BROWN fox.")
Both of these methods support truncation after STRING conversion. Note that the truncation length must include the appended blank:
WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",9,6),! WRITE $SYSTEM.SQL.STRING("The quick, BROWN fox.",6)
The %SQLUPPER function is the preferred way in SQL to convert a data value for not case-sensitive comparison or collation. Refer to %SQLUPPER for further information on case transformation functions.
The following example selects Name values that begin with “od”:
SELECT Name FROM Sample.Person WHERE %STRING(Name) %STARTSWITH %STRING 'od'
Because %STRING removes punctuation and blank spaces and performs not case-sensitive collation, this example returns names such as ”Odem”, O'Donnell”, “ODonnell”, and “O Donnell”.
The following example uses %STRING 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 %STRING. The ORDER BY clause uses this two-character field to put the rows in a rough collation sequence:
SELECT Name, %STRING(Name,3) AS FirstTwo FROM Sample.Person ORDER BY FirstTwo
The following example applies %STRING to a subquery:
SELECT TOP 5 Name, %STRING((SELECT Name FROM Sample.Company),10) AS Company FROM Sample.Person
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|