Oracle PL/SQL Programming, Third Edition

Name

CHR

Synopsis

The CHR function is the inverse of ASCII. It returns a VARCHAR2 character (length 1) that corresponds to the location in the collating sequence provided as a parameter. The specification of the CHR function is:

FUNCTION CHR (code_location IN NUMBER) RETURN VARCHAR2

where code_location is the number specifying the location in the collating sequence.

The CHR function is especially valuable when you need to make reference to a nonprintable character in your code. For example, the location in the standard ASCII collating sequence for the newline character is 10 (the ASCII linefeed). The CHR function gives you a way to search for the newline character in a string, and perform operations on a string based on the presence of that control character.

You can also insert a linefeed into a character string using the CHR function. For example, suppose you have to build a report that displays the address of a company. A company can have up to four address strings (in addition to city, state, and zip code). You need to put each address string on a new line, but you don’t want any blank lines embedded in the address. The following SELECT will not do the trick:

SELECT name, address1, address2, address3, address4,
       city || ', ' || state || ' ' || zipcode location
  FROM company;

Assuming that each column (report field) goes on a new line, you will end up using six lines per address, no matter how many of these address strings are NULL. For example:

HAROLD HENDERSON 22 BUNKER COURT SUITE ...