Oracle CHR Function

The OracleCHR() function converts an ASCII code, which is a number,  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 number.

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.
• If thenumeric_expression is less than 0, the CHR() function will issue an error.
• If n is greater than 256, the function returns the binary equivalent of n mod 256.
• The CHR() function returns NULL if the numeric_expresion is NULL.

Examples #

The following statement returns characters of the ASCII code 83 and 115:

SELECT
  CHR( 83 ),
  CHR( 115 )
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Try it

The following statement uses the the CHR() function with NULL:

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

Try it

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)

Try it

Output:

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)

Summary #

• Use the Oracle CHR() function to get the corresponding character of an ASCII code.