If you've ever needed to use a special character in Oracle SQL, like a line break or a copyright symbol (©), the CHR function is the tool you need. This guide will simply explain what the Oracle CHR function is and how to use it with clear examples.
What is the CHR Function in Oracle?
The CHR function in Oracle converts a number into its corresponding character.
Think of it like a "number-to-letter" converter. Every character on a computer (like 'A', 'b', '!', or even a line break) has a corresponding number. CHR lets you get the character if you know its number.
This is most commonly used when you need to insert a special character that is hard to type, like a copyright symbol (©), a trademark symbol (™), or a newline (a line break).
CHR Function Syntax
The syntax for the CHR function is straightforward:
CHR(n [USING NCHAR_CS])
Let's break that down:
n: This is the number of the character you want. It must be aNUMBER(or a value that can be converted to one).USING NCHAR_CS(Optional): This tells Oracle to look up the number in the "national character set" instead of the default database character set. You usually don't need this unless you are working with specific international characters.
Oracle CHR Function Examples
Here are two practical, real-world examples of how to use CHR in an Oracle database.
Example 1: Building a Simple String with CHR
You can use CHR to build a string from scratch, character by character. This is common in the official documentation. The || symbol is used to join (concatenate) strings together.
Based on the standard ASCII character set:
- The number for 'C' is 67.
- The number for 'A' is 65.
- The number for 'T' is 84.
Let's use CHR to build the word "CAT".
Query:
-- We select from DUAL, which is a special one-row, -- one-column table in Oracle used for quick tests. SELECT CHR(67) || CHR(65) || CHR(84) AS Word FROM DUAL;
Result:
WORD ---- CAT
Example 2: Using CHR for Special (Non-Printable) Characters
This is the most common real-world use for CHR. Imagine you want to format a string to have a line break in the middle, but you can't just press "Enter" inside your SQL query.
The number for a "newline" or "line feed" character is 10.
Let's create a single string that says "Hello" on the first line and "World" on the second.
Query:
SELECT 'Hello' || CHR(10) || 'World' AS Formatted_String FROM DUAL;
Result:
When you run this in a SQL tool (like SQL Developer or SQL*Plus), it will interpret CHR(10) as a line break. The output will look like this:
FORMATTED_STRING ----------------- Hello World
You can use this same idea to add other special characters. For example, CHR(169) is the copyright symbol (©), and CHR(9) is a tab.
A Quick Note on Character Sets
It's important to know that these numbers can change. The number 67 means 'C' on most modern computers, which use a set called ASCII. Some very old mainframes use a different set called EBCDIC, where 'C' is 195. For multi-byte character sets (like UTF-8), you must provide the single, complete number for the character.
