The Oracle CHR, ASCII, NCHR, and ASCIISTR functions are useful when translating between text characters and their ASCII codes. Learn all about these functions in this article.
Purpose of the Oracle CHR, ASCII, NCHR, and ASCIISTR Functions
These four functions are similar, which is why I have combined them into a single guide.
The purpose of the Oracle CHR function is to allow you to enter a number code and return an ASCII character. It’s the opposite of the ASCII function.
The Oracle ASCII function allows users to convert a single character into a number that represents the character. It’s how you get an ASCII value of a CHAR in Oracle.
The Oracle NCHR function returns a character based on the specified number code in the national character set. It’s very similar to the CHR function, but it uses the national character set.
The Oracle ASCIISTR function converts a string in any character set to a string in the database character set. If there are any characters that are not ASCII, they are converted to a format of \xxxx, where xxxx is the UTF-16 code.
Get Your SQL Cheat Sheet
Oracle CHR Function Syntax and Parameters
The syntax of the CHR function is:
CHR ( number_code [USING NCHAR_CS] )
The pameters of the CHR function are:
- number_code (mandatory): This is the number code to look up the ASCII character.
- USING NCHAR_CS (optional): Adding this parameter means that you will use the national character set.
The return value is a single character that represents the supplied number.
Oracle ASCII Function Syntax and Parameters
The syntax of the ASCII function is:
ASCII ( charvalue )
The return type of this function is a NUMBER.
The parameters of the ASCII function are:
- charvalue (mandatory): This is a value that is to be converted into its ASCII code. It can be of type CHAR, NCHAR, VARCHAR2, or NVARCHAR2.
Some things to note about this function:
- If more than one character is entered as the charvalue parameter, the function will return the value for the first character, and ignore all of the other characters.
- If your database is in the EBCDIC character set, then this function returns the EBCDIC value (there is no EBCDIC character function).
For a list of the ASCII codes in Oracle, you can refer to this page.
Oracle NCHR Function Syntax and Parameters
The syntax of the NCHR function is:
NCHR ( number_code )
The parameters of the NCHR function are:
- number_code (mandatory): The numeric value that identifies a single character.
The number_code is a NUMBER value, or anything that can be implicitly converted to a number.
The return value of this function is always an NVARCHAR2 data type.
The NCHR function is the same as using the CHR function with the parameter USING NCHAR_CS.
Oracle ASCIISTR Syntax and Parameters
The syntax of the ASCIISTR function is:
ASCIISTR ( charvalue )
The parameters are:
charvalue (mandatory): This is the string value that is to be converted to an ASCII string.
The return type is an ASCII string. So this is how you convert a string to ASCII values in Oracle.
What Does the Oracle CHR(10) Function Mean?
This is often used to insert a line feed into a string.
The number 10 represents the line feed character, so using CHR(10) will return a line feed character to split a line of text.
CHR(9) is a horizontal tab.
CHR(10) is a line feed.
CHR(13) is a carriage return.
Is There A TO_ASCII In Oracle?
No, there is no function called TO_ASCII, but the ASCII function may be what you’re looking for.
Examples of the Oracle CHR, ASCII, and NCHR Functions
Here are some examples of these function. I find that examples are the best way for me to learn about code, even with the explanation above.
This example shows a single lower-case character.
SELECT CHR(97) AS CHR_TEST FROM dual;
The result is “a” because that is the character for the supplied value.
This example shows a single upper-case character.
SELECT CHR(80) AS CHR_TEST FROM dual;
The result is “P” because that is the character for the supplied value.
This example uses concatenation of several characters
SELECT CHR(89) || CHR(101) || CHR(115) AS CHR_TEST FROM dual;
The result is “Yes” because that’s what the three characters translate to.
This example uses CHR(10).
SELECT CHR(83) || CHR(10) || CHR(84) AS CHR_TEST FROM dual;
You can find a list of the ASCII codes here.The result is the letter S, then a line break, then the letter T.
This example uses a single character in the ASCII function.
SELECT ASCII('B') AS asciival FROM dual;
This example uses a special character.
SELECT ASCII('é') FROM dual;
This example uses multiple characters as a parameter.
SELECT ASCII('Complete') FROM dual;
The function only shows the ASCII value for the first character, which is “C”.
This example uses the ASCII function in the WHERE clause. I’ve used the SUBSTR function to consider only the first character of the last_name field.
SELECT first_name, last_name FROM student WHERE ASCII(SUBSTR(last_name, 1, 1)) = '67';
You can see that there are two records, both with a last_name beginning with C. This is because the ASCII value of C is 67.
This is a simple example of the NCHR function.
SELECT NCHR(120) FROM dual;
This example uses different CHR functions and the NCHR function.
SELECT CHR(161) AS CHR_FN, CHR(161 USING NCHAR_CS) AS CHR_USING, NCHR(161) AS NCHR_FN FROM dual;
As you can see, using CHR has shown a NULL value. However, using NCHR has shown a value.
This is an example of ASCIISTR with a standard value.
SELECT ASCIISTR('Monday') AS output_value FROM dual;
This is an example of ASCIISTR with some unicode characters.
SELECT ASCIISTR('TuësdĂy)') AS output_value FROM dual;
This is another example of ASCIISTR with unicode characters.
SELECT ASCIISTR('WÈÐńėśĎày') AS output_value FROM dual;
So, that’s how you can use the CHR, ASCII, NCHR, and ASCIISTR functions in Oracle.
You can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!