For different kinds of applications, it might be necessary to recode values, because different operators demand it.
CHR - NUM Conversion of character values into numbers and vice versa.
Example: Searching for patterns in a sequence of digits
SELECT * FROM hotel
WHERE CHR(zip) LIKE '43%'
UPPER - LOWER Conversion of lower case letters into upper case letters and vice versa (thus unifying the kind of letters).
Example: Searching for values irrespective of small or capital letters
SELECT * FROM hotel
WHERE UPPER (name) = 'STAR'
ASCII - EBCDIC Conversion of an ASCII-coded character into the corresponding EBCDIC representation and vice versa.
Example: Outputting a sorted result table in EBCDIC order
SELECT EBCDIC (name) FROM
WHERE ...
ORDER BY 1
See also the section Ordering Rows.
MAPCHAR Conversion of country-specific letters into another representation. This is done to enable a useful alphabetical order.
ALPHA Conversion of an ASCII- or EBCDIC-coded character into another one- or two-character representation defined in the DEFAULTMAP. This function internally uses MAPCHAR and additionally converts the character into an upper case character. ALPHA also influences the sort sequence.
HEX Conversion into the hexadecimal representation.
CHAR Conversion of a date or time value into a character string.
SOUNDEX Conversion of a character string into a representation generated by the so-called SOUNDEX algorithm. This representation can be used if the exact spelling of a term is not known ("sounds like").
VALUE Conversion of a NULL value into another value. In the following example, the title does not occur in the output list, and for companies, the word 'Company' is to be output as 'firstname' instead of the NULL value.
SELECT VALUE (firstname, 'Company')
firstname, name
FROM customer
DECODE Conversion of expressions according to their values. The designations for roomtype are to be replaced on output by a code defined by using the DECODE function.
SELECT hno, price,
DECODE (roomtype, 'single', 1,
'double', 2,
'suite' , 3)
code_of_room
FROM room