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 passing 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-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.

Was this tutorial helpful?