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)
Oracle CHR function Example

The following statement illustrates how to pass NULL to the CHR() function:

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

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:

Control characterValue
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-2016Code 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.

Was this tutorial helpful?