CHR() , ASCII( )Functions in Oracle

CHR() Function in Oracle

The CHR() function in Oracle returns the character corresponding to the given ASCII code. It is useful for generating special characters, line breaks, and non-printable characters.

---

🔹 Syntax

CHR(n)

• n → ASCII code of the character you want to retrieve.

---

🔹 Examples

1️⃣ Get a Character from ASCII Code

SELECT CHR(65) FROM DUAL;

🔹 Output: 'A'
(ASCII code 65 corresponds to the letter 'A')

SELECT CHR(97) FROM DUAL;

🔹 Output: 'a'
(ASCII code 97 corresponds to the letter 'a')

---

2️⃣ Combine Multiple Characters

SELECT CHR(72) || CHR(69) || CHR(76) || CHR(76) || CHR(79) FROM DUAL;

🔹 Output: 'HELLO'
(ASCII codes 72, 69, 76, 76, and 79 correspond to 'H', 'E', 'L', 'L', 'O')

---

3️⃣ Insert Special Characters (New Line, Tab, etc.)

• New Line (CHR(10))

SELECT 'Line 1' || CHR(10) || 'Line 2' FROM DUAL;

🔹 Output:

Line 1
Line 2

(CHR(10) represents a newline character)

• Tab (CHR(9))

SELECT 'Col1' || CHR(9) || 'Col2' FROM DUAL;

🔹 Output: Col1 Col2
(CHR(9) represents a tab space)

• Single Quote (CHR(39))

SELECT 'It' || CHR(39) || 's Oracle' FROM DUAL;

🔹 Output: 'It's Oracle'
(CHR(39) represents a single quote ')

---

🔹 Use Case: Removing Control Characters

If a column contains unwanted control characters (like newlines or tabs), you can remove them using REPLACE() and CHR():

SELECT REPLACE(column_name, CHR(10), ' ') FROM table_name;

(This replaces newline characters with spaces)

---

🔹 Summary

ASCII Code	Character	Description
10	\n	New Line
9	\t	Tab
39	'	Single Quote
65	A	Uppercase 'A'
97	a	Lowercase 'a'

🔹 More Practical Uses of CHR() in Oracle

---

1️⃣ Using CHR() for CSV Data Formatting

If you want to generate a comma-separated list from multiple rows, you can use LISTAGG() with CHR(44) (which is the ASCII code for a comma ,).

SELECT LISTAGG(employee_name, CHR(44)) WITHIN GROUP (ORDER BY employee_name) AS csv_list
FROM employees;

🔹 Output: 'Alice,Bob,Charlie,David,Emma'
(Each name is separated by a comma)

---

2️⃣ Handling Special Characters in Strings

If a string contains quotes or special symbols, CHR() helps in escaping them.

SELECT 'This is ' || CHR(39) || 'Oracle' || CHR(39) || ' SQL' FROM DUAL;

🔹 Output: 'This is 'Oracle' SQL'
(CHR(39) adds a single quote in the string)

---

3️⃣ Removing New Lines or Tabs from a Column

Sometimes, text data contains hidden newline (CHR(10)) or tab (CHR(9)) characters, which can be removed like this:

SELECT REPLACE(REPLACE(column_name, CHR(10), ' '), CHR(9), ' ') FROM my_table;

This replaces newlines and tabs with spaces.

---

4️⃣ Adding Line Breaks in SQL Queries

To display multi-line messages, you can use CHR(10):

SELECT 'Hello,' || CHR(10) || 'Welcome to Oracle!' FROM DUAL;

🔹 Output:

Hello,
Welcome to Oracle!

---

5️⃣ Generate Passwords or Random Strings with CHR()

If you want to create a random password, you can use DBMS_RANDOM.VALUE() along with CHR():

SELECT CHR(FLOOR(DBMS_RANDOM.VALUE(65,90))) || 
       CHR(FLOOR(DBMS_RANDOM.VALUE(97,122))) || 
       CHR(FLOOR(DBMS_RANDOM.VALUE(48,57))) || 
       CHR(FLOOR(DBMS_RANDOM.VALUE(33,47))) AS random_password
FROM DUAL;

🔹 Output: 'Az9@' (Random password with uppercase, lowercase, numbers, and symbols)

---

6️⃣ Convert ASCII Code to Character Dynamically

If you have ASCII values stored in a table, you can retrieve their corresponding characters:

SELECT CHR(ascii_code_column) FROM ascii_table;

🔹 Example: If ascii_code_column = 65, output will be 'A'.

---

7️⃣ Generate ASCII Table in Oracle

If you want to generate an ASCII table from 32 to 126 (printable characters), you can use CONNECT BY:

SELECT LEVEL AS ascii_code, CHR(LEVEL) AS character
FROM DUAL
CONNECT BY LEVEL <= 126;

🔹 Output:

ASCII_CODE  CHARACTER
---------  ---------
32         (space)
33	!
34	"
35	#
36	$
37	%
38	&
39	'
40	(
41	)
42	*
43	+
44	,
45	-
46	.
47	/
48	0
49	1
50	2
51	3
52	4
53	5
54	6
55	7
56	8
57	9
58	:
59	;
60	<
61	=
62	>
63	?
64	@
65	A
66	B
67	C
68	D
69	E
70	F
71	G
72	H
73	I
74	J
75	K
76	L
77	M
78	N
79	O
80	P
81	Q
82	R
83	S
84	T
85	U
86	V
87	W
88	X
89	Y
90	Z
91	[
92	\
93	]
94	^
95	_
96	`
97	a
98	b
99	c
100	d
101	e
102	f
103	g
104	h
105	i
106	j
107	k
108	l
109	m
110	n
111	o
112	p
113	q
114	r
115	s
116	t
117	u
118	v
119	w
120	x
121	y
122	z
123	{
124	|
125	}
126	~

---

8️⃣ Convert Characters to ASCII Code Using ASCII()

The opposite of CHR() is ASCII(), which returns the ASCII code of a given character:

SELECT ASCII('A') FROM DUAL;

🔹 Output: 65

SELECT ASCII('z') FROM DUAL;

🔹 Output: 122

---

🔹 Summary of CHR() Use Cases

Use Case	Example
Format CSV Output	LISTAGG(name, CHR(44))
Escape Single Quotes	`'It'
Remove New Lines/Tabs	REPLACE(col, CHR(10), ' ')
Multi-line Output	`'Hello'
Generate Random Strings	CHR(DBMS_RANDOM.VALUE(...))
Convert ASCII to Char	CHR(65) → 'A'
Convert Char to ASCII	ASCII('A') → 65