CHR() Function in Oracle

by

In Oracle, the CHR() function returns a character based on the code values provided as an argument.

More specifically, it returns the character having the binary equivalent to its argument as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set.

Syntax

The syntax goes like this:

CHR(n [ USING NCHAR_CS ])

Where n is a NUMBER value, or any value that can be implicitly converted to NUMBER.

The USING NCHAR_CS argument is an optional argument that allows you to specify the national character set.

Example

Here’s a simple example to demonstrate:

SELECT CHR(65)
FROM DUAL;

Result:

   CHR(65) 
__________ 
A

Here I provided a number, and CHR() returned the corresponding character. In this case, the integer 65 maps to the uppercase letter A.

Case Sensitivity

Here’s an example that distinguishes an uppercase letter from its lowercase counterpart:

SELECT 
    CHR(72),
    CHR(104)
FROM DUAL;

Result:

   CHR(72)    CHR(104) 
__________ ___________ 
H          h

Return a Multiple Character String

To produce a multi-character string, we can concatenate multiple CHR() functions:

Example:

SELECT CHR(65) || CHR(77)
FROM DUAL;

Result:

   CHR(65)||CHR(77) 
___________________ 
AM

The NCHAR_CS Argument

Here’s an example of passing the (optional) second argument:

SELECT CHR (257 USING NCHAR_CS)
FROM DUAL;

Result:

   CHR(257USINGNCHAR_CS) 
________________________ 
ā

This is the same as using the NCHR() function.

Null Values

Passing null returns null:

SET NULL 'null';

SELECT CHR(null)
FROM DUAL;

Result:

   CHR(NULL) 
____________ 
null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling CHR() without passing any arguments returns an error:

SELECT CHR()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT CHR()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT CHR(1, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT CHR(1, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: