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().
<source lang="sql">
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></source>
Use CHR() function to create special char
<source lang="sql">
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></source>
