The SQL CHR, CHAR, ASCII, NCHR, NCHAR, 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 SQL CHR, CHAR, ASCII, NCHR, NCHAR, and ASCIISTR Functions
These functions are similar, which is why I have combined them into a single guide.
These functions will
- ASCII function will allow you to convert a single character into a number that represents the character. It’s how you get an ASCII value of a CHAR in SQL.
- CHR/CHAR function will allow you to enter a number code and return an ASCII character. It’s the opposite of the ASCII function.
- NCHR/NCHAR 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.
- 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.
These functions are only available in some databases:
| Database | CHR | CHAR | ASCII | NCHR | NCHAR | ASCIISTR |
| Oracle | Y | N | Y | Y | N | Y |
| SQL Server | N | Y | Y | N | Y | N |
| MySQL | N | Y | Y | N | N | N |
| Postgres | Y | N | Y | N | N | N |
SQL CHR and CHAR Function Syntax and Parameters
The syntax of the CHR function is:
CHR ( number_code [USING NCHAR_CS] )
The syntax of the CHAR function is:
CHAR ( number_code )
The parameters of the CHR and CHAR 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. This applies to Oracle only.
The return value is a single character that represents the supplied number.
SQL 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 SQL, you can refer to this page.
SQL NCHR and NCHAR Function Syntax and Parameters
The syntax of the NCHR function is:
NCHR ( number_code )
The syntax of the NCHAR function is:
NCHAR ( number_code )
The parameters of the NCHR and NCHAR functions 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 the NCHR function is always an NVARCHAR2 data type, and for NCHAR it is NVARCHAR.
The NCHR function is the same as using the CHR function with the parameter USING NCHAR_CS.
SQL 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 SQL.
What Does the SQL 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 SQL CHR, ASCII, and NCHR Functions
Here are some examples of these functions.
Example 1
This example shows a single lower-case character.
SELECT CHR(97);Result:
a
The result is “a” because that is the character for the supplied value.
Example 2
This example shows a single upper-case character.
SELECT CHR(80);Result:
P
The result is “P” because that is the character for the supplied value.
Example 3
This example uses the concatenation of several characters
SELECT CHR(89) || CHR(101) || CHR(115);Result:
Yes
The result is “Yes” because that’s what the three characters translate to.
Example 4
This example uses CHR(10).
SELECT CHR(83) || CHR(10) || CHR(84);Result:
S T
You can find a list of the ASCII codes here. The result is the letter S, then a line break, and then the letter T.
Example 5
This example uses a single character in the ASCII function.
SELECT ASCII('B');Result:
66
Example 6
This example uses a special character.
SELECT ASCII('é');Result:
50089
Example 7
This example uses multiple characters as a parameter.
SELECT ASCII('Complete');Result:
67
The function only shows the ASCII value for the first character, which is “C”.
Example 8
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';Result:
| FIRST_NAME | LAST_NAME |
| Tom | Capper |
| Andrew | Cooper |
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.
Example 9
This is a simple example of the NCHR function.
SELECT NCHR(120);Result:
x
Example 10
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;Result:
| CHR_FN | CHR_USING | NCHR_FN |
| (null) | ¡ | ¡ |
As you can see, using CHR has shown a NULL value. However, using NCHR has shown a value.
Example 11
This is an example of ASCIISTR with a standard value.
SELECT ASCIISTR('Monday');Result:
Monday
Example 12
This is an example of ASCIISTR with some unicode characters.
SELECT ASCIISTR('TuësdĂy)');Result:
Tu\00EBsd\0102y)
Example 13
This is another example of ASCIISTR with unicode characters.
SELECT ASCIISTR('WÈÐńėśĎày');Result:
W\00C8\00D0\0144\0117\015B\010E\00E0y
So, that’s how you can use the CHR, CHAR, ASCII, NCHR, NCHAR, and ASCIISTR functions in SQL.
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!