Caché SQL Reference
%ALPHAUP
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
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 all alphabetic characters to uppercase (i.e., the ALPHAUP format) and removes all blanks and punctuation characters except commas and question marks.
%ALPHAUP (unlike %SQLUPPER and %STRING) does not force numerics to be interpreted as a string. Caché Basic converts a numeric to canonical form (removing leading and trailing zeros, expanding exponents, etc.) before passing the numeric to the function. (Caché Basic does not convert numeric strings to canonical form.) %ALPHAUP then removes the decimal separator character, the minus sign, and any other punctuation or 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 Caché 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 Caché 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