Oracle PL/SQL Tutorial/Character String Functions/CHR

CHR(x) gets the character with the ASCII value of x

CHR() and ASCII() have the opposite effect.

The following example gets the characters with the ASCII value of 97, 65, 122, 90, 48, and 57 using CHR().

SQL> SELECT CHR(97), CHR(65), CHR(122), CHR(90), CHR(48), CHR(57) FROM dual;
C C C C C C
- - - - - -
a A z Z 0 9
SQL>

Use CHR() function to create special char

SQL>
SQL> CREATE TABLE customer
  2  (customer_id        NUMBER(7),
  3   customer_name      VARCHAR2(50),
  4   phone              VARCHAR2(15),
  5   address            VARCHAR2(400),
  6   city               VARCHAR2(35),
  7   state              VARCHAR2(30),
  8   country            VARCHAR2(30),
  9   zip_code           VARCHAR2(10),
 10   credit_rating      VARCHAR2(9),
 11   sales_rep_id       NUMBER(7),
 12   region_id          NUMBER(7),
 13   comments           VARCHAR2(255),
 14   preferred_customer VARCHAR2(1) DEFAULT "N" NOT NULL,
 15   shipping_method    VARCHAR2(1) DEFAULT "M" NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, "Jane",    "111-1111", "7 AVE","SAO", NULL, "BRAZIL", NULL, "EXCELLENT",12, 2, "A", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (202, "Todd",    "222-2222", "6 BLVD.","OSAKA", NULL, "JAPAN", NULL, "POOR", 14, 4, "B", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (203, "Sharon",  "333-3333", "1 STREET", "NEW DELHI", NULL, "INDIA", NULL, "GOOD", 14, 4,"C", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (204, "Hong",    "444-4444", "2 STREET","SEATTLE", "WASHINGTON", "USA", "98101", "EXCELLENT",11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (205, "Anderson","555-5555", "5 ROAD", "HONG KONG", NULL, NULL,NULL, "EXCELLENT", 15, 4, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (206, "Bob",     "666-6666", "1 ROAD","CANNES", NULL, "FRANCE", NULL, "EXCELLENT", 15, 5,"D", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (207, "Cat",     "777-7777", "6 STREET","LAGOS", NULL, "NIGERIA", NULL, "GOOD", NULL, 3, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (208, "Doge",    "888-8888", "4 RASSE", "STUTTGART", NULL, "GERMANY", NULL, "GOOD", 15, 5,"E", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (209, "Black",   "999-9999", "2 MAR","SAN PEDRO DE MACON""S", NULL, "DOMINICAN REPUBLIC",NULL, "EXCELLENT", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (210, "Red",     "000-0000", "3 ARO","NOGALES", NULL, "MEXICO", NULL, "EXCELLENT", 12, 2,"Customer is difficult to reach by phone.  Try mail.","N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (211, "Ted",     "123-1231", "7 MOD", "PRAGUE",NULL, "CZECHOSLOVAKIA", NULL, "EXCELLENT", 15, 5, NULL,"N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (212, "Homas",   "124-1234", "5 COR","ALEXANDRIA", NULL, "EGYPT", NULL, "EXCELLENT", 13, 3,"F", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (213, "Look",    "555-6281", "4 STREET", "SAN FRANCISCO", "CA", "USA", "94117","EXCELLENT", 11, 1, "G", "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (214, "Yellow",  "555-7171", "4 STREET","BUFFALO", "NY", "USA", "14202", "POOR", 11, 1, NULL, "N", "M");
1 row created.
SQL> INSERT INTO customer VALUES (215, "White",   "337-3892", "6 YEK","SAINT PETERSBURG", NULL, "RUSSIA", NULL, "POOR",15, 5, "T", "N", "M");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT customer_name || CHR(10) ||
  2         address || CHR(10) ||
  3         customer_csz(customer_id) full_address
  4  FROM   customer;
FULL_ADDRESS
--------------------------------------------------------------------------------
Jane
7 AVE
SAO, BRAZIL
Todd
6 BLVD.
OSAKA, JAPAN
Sharon
1 STREET
NEW DELHI, INDIA
FULL_ADDRESS
--------------------------------------------------------------------------------
Hong
2 STREET
SEATTLE, WASHINGTON 98101 USA
Anderson
5 ROAD
HONG KONG,
Bob
1 ROAD
FULL_ADDRESS
--------------------------------------------------------------------------------
CANNES, FRANCE
Cat
6 STREET
LAGOS, NIGERIA
Doge
4 RASSE
STUTTGART, GERMANY
Black
FULL_ADDRESS
--------------------------------------------------------------------------------
2 MAR
SAN PEDRO DE MACON"S, DOMINICAN REPUBLIC
Red
3 ARO
NOGALES, MEXICO
Ted
7 MOD
PRAGUE, CZECHOSLOVAKIA

FULL_ADDRESS
--------------------------------------------------------------------------------
Homas
5 COR
ALEXANDRIA, EGYPT
Look
4 STREET
SAN FRANCISCO, CA 94117 USA
Yellow
4 STREET
BUFFALO, NY 14202 USA
FULL_ADDRESS
--------------------------------------------------------------------------------
White
6 YEK
SAINT PETERSBURG, RUSSIA

15 rows selected.
SQL>
SQL>
SQL>
SQL> drop table customer;
Table dropped.
SQL>