Caché SQL Reference
%ALPHAUP
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Deprecated. A collation function that converts alphabetic characters to the ALPHAUP collation format.
Synopsis
%ALPHAUP(expression) 
%ALPHAUP 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).
Description
This is a deprecated collation function. Please refer to %SQLUPPER for new development. %SQLUPPER provides superior handling for non-alphabetic characters.
%ALPHAUP converts expression to the ALPHAUP format:
%ALPHAUP (unlike %SQLUPPER and %STRING) does not force numerics to be interpreted as a string. SQL converts a numeric to canonical form (removing leading and trailing zeros, expanding exponents, etc.) before passing the numeric to the function. (SQL does not convert numeric strings to canonical form.) %ALPHAUP 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, %ALPHAUP must be used with caution on any expression containing non-alphabetic information.
%ALPHAUP is a Caché SQL extension and is intended for SQL lookup queries.
You can perform the same collation conversion in ObjectScript using the Collation() method of the %SYSTEM.Util class:
  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",6)
 
This function can also be invoked from ObjectScript using the ALPHAUP() method call:
  WRITE $SYSTEM.SQL.ALPHAUP("The quick, BROWN fox.")
 
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
In the following example, %ALPHAUP is used to convert the Name field to uppercase, so that the contains operator ([) can test for the letter “Y”. This query returns all names in Sample.Person that contain the letter “Y”, either uppercase or lowercase:
SELECT Name FROM Sample.Person
WHERE %ALPHAUP(Name) [ 'Y'
 
The following embedded SQL example shows how %ALPHAUP can be used with Unicode alphabetic characters, in this case Greek letters:
  IF $SYSTEM.Version.IsUnicode() {
  SET greek=$CHAR(952,945,955,945,963,963,945)
  WRITE !,"lowercase Greek: ",greek
  &sql(SELECT %ALPHAUP(:greek)
       INTO :capgreek
       FROM Sample.Person)
  WRITE !,"uppercase Greek: ",capgreek
  }
  ELSE {WRITE "This example requires a Unicode installation of Caché"}
 
(Note that the above example requires a Unicode installation of Caché.)
The following cautionary examples show how %ALPHAUP may treat as identical strings that are in fact quite different:
SELECT %ALPHAUP('Max Wells'),%ALPHAUP('Maxwell S.'),
   %ALPHAUP('Release 3.2'),%ALPHAUP('Re: Lease 32'),
   %ALPHAUP('12/2/04'),%ALPHAUP('1/22/04'),
   %ALPHAUP('-36.5 degrees'),%ALPHAUP('365 Degrees')
 
See Also
%SQLUPPER
Collation chapter in Using Caché SQL