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 ...
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.