%ALPHAUP

Deprecated. A collation function that converts alphabetic characters to the ALPHAUP collation format.

Synopsis

%ALPHAUP(expression) 
%ALPHAUP 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).

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:

Converts all letters to uppercase.
Removes all punctuation characters, except the comma and question mark. (%STRING removes all punctuation characters, except the comma.)
Removes all blank spaces (leading, trailing, and embedded).

%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()Opens in a new tab method of the %SYSTEM.UtilOpens in a new tab class:

  WRITE $SYSTEM.Util.Collation("The quick, BROWN fox.",6)

This function can also be invoked from ObjectScript using the ALPHAUP()Opens in a new tab 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')