Oracle CHR

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, the CHR() 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 returns NULL if the numeric_expresion is NULL.

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 passing NULL to the CHR() function:

SELECT
  CHR( NULL )
FROM
  DUAL;
Code language: SQL (Structured Query Language) (sql)

In this example, the CHR() function returns NULLas expected.

Remarks

You can use the CHR() function to insert control characters into a string. The following table illustrates the commonly used control characters:

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.