The OracleCHR()
function converts an ASCII code, which is a numeric value between 0 and 225, to a character. The Oracle CHR()
function is the opposite of the ASCII()
function.
Syntax
CHR(numeric_expression)
Code language: SQL (Structured Query Language) (sql)
Arguments
The CHR()
function accepts one argument:
- numeric_expression is a numeric value or an expression that returns a numeric value from 0 through 255.
Return values
- If the
numeric_expression
is from 0 through 255, theCHR()
function returns a character that represents the number in the ASCII table. - In case the numeric_expression is less than 0 or greater than 255, the
CHR()
function will issue an error of"ORA-01426: numeric overflow"
. - The
CHR()
function returnsNULL
if thenumeric_expresion
isNULL
.
Examples
The following statement returns characters of the ASCII code 65
and 97
:
SELECT
CHR( 83 ),
CHR( 115 )
FROM
dual;
Code language: SQL (Structured Query Language) (sql)
The following statement illustrates how to pass NULL
to the CHR()
function:
SELECT
CHR( NULL )
FROM
DUAL;
Code language: SQL (Structured Query Language) (sql)
In this example, the CHR()
function returns NULL
as expected.
Remarks
You can use the CHR() function to insert control characters into a string. The following table illustrates the commonly used control characters:
Control character | Value |
---|---|
Carriage return | CHR(13) |
Line feed | CHR(10) |
Tab | CHR(9) |
Let’s see the following employees
table in the sample database:
The following statement uses the CHR()
function to output the first five employees of the company:
SELECT
first_name || ' ' || last_name ||
CHR( 9 ) || ' joined on ' || CHR( 9 ) ||
to_char(hire_date,'DD-MON-YYYY') first_5_employees
FROM
employees
ORDER BY
hire_date
FETCH
FIRST 5 ROWS ONLY;
Code language: SQL (Structured Query Language) (sql)
FIRST_5_EMPLOYEES
--------------------------------------------------------------------------------
Louie Richardson joined on 03-JAN-2016
Elizabeth Dixon joined on 04-JAN-2016
Ella Wallace joined on 05-JAN-2016
Blake Cooper joined on 13-JAN-2016
Thea Hawkins joined on 13-JAN-2016
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the Oracle CHR()
function to get the corresponding character of an ASCII code.