ASCII and CHR Functions in SQL

1. ASCII(): Character to Code Point

The ASCII() function accepts a string and returns the integer code point of its first character. All four databases implement it, and for the 128 standard ASCII values (0 through 127) the results match across engines.

-- Works identically in PostgreSQL, MySQL, SQL Server, Oracle
SELECT ASCII('A');   -- 65
SELECT ASCII('a');   -- 97
SELECT ASCII('0');   -- 48
SELECT ASCII(' ');   -- 32
SELECT ASCII('Hello'); -- 72 (first character only)

The values correspond to the decimal column in the ASCII table: 65 for A, 97 for a, 48 for the digit 0. Only the first character is evaluated. Passing an empty string returns 0 in MySQL and SQL Server, and 0 in PostgreSQL, but raises an error in Oracle.

2. CHR() vs CHAR(): Code Point to Character

The reverse function has two names depending on the database:

Database	Function	Example	Result
PostgreSQL	CHR(n)	CHR(65)	'A'
Oracle	CHR(n)	CHR(65)	'A'
MySQL	CHAR(n)	CHAR(65)	'A'
SQL Server	CHAR(n)	CHAR(65)	'A'

-- PostgreSQL / Oracle
SELECT CHR(65);   -- 'A'
SELECT CHR(10);   -- newline character
SELECT CHR(0);    -- NULL byte (PostgreSQL), error in Oracle

-- MySQL / SQL Server
SELECT CHAR(65);   -- 'A'
SELECT CHAR(10);   -- newline character
SELECT CHAR(NULL); -- NULL

MySQL's CHAR() accepts multiple arguments and concatenates the results: CHAR(72, 101, 108, 108, 111) returns 'Hello'. None of the other engines support this.

PostgreSQL's CHR(0) is problematic. It returns a NUL byte, which C-based string functions treat as a terminator. Storing it in a text column may truncate data in some client libraries. Oracle raises ORA-06502 for CHR(0).

3. UNICODE() for Non-ASCII Characters

The ASCII() function technically returns the code point based on the column's encoding, not strictly the ASCII value. For Unicode databases (UTF-8 or UTF-16), ASCII() returns the Unicode code point for characters beyond 127. SQL Server provides a dedicated UNICODE() function for clarity:

-- SQL Server
SELECT UNICODE(N'e'); -- 233 (U+00E9, e with acute)
SELECT ASCII('e');    -- 233 (same result for varchar)

-- PostgreSQL (UTF-8 database)
SELECT ASCII('e');    -- 233

The distinction matters when processing data from multiple character encodings. A byte value of 233 means different characters in ISO 8859-1 versus Windows-1252 versus UTF-8. Use the encoding comparison tool to see exactly where they diverge.

4. Practical Examples

Find rows containing non-ASCII characters

-- PostgreSQL
SELECT id, name FROM customers
WHERE name ~ '[^\x00-\x7F]';

-- MySQL
SELECT id, name FROM customers
WHERE name REGEXP '[^\x00-\x7F]';

-- SQL Server
SELECT id, name FROM customers
WHERE name LIKE '%[^' + CHAR(0) + '-' + CHAR(127) + ']%' COLLATE Latin1_General_BIN;

-- Oracle
SELECT id, name FROM customers
WHERE REGEXP_LIKE(name, '[^' || CHR(0) || '-' || CHR(127) || ']');

This query catches accented characters, CJK text, emoji, and invisible Unicode characters that break downstream systems expecting pure ASCII.

Strip non-ASCII characters

-- PostgreSQL
SELECT REGEXP_REPLACE(name, '[^\x20-\x7E]', '', 'g') AS clean_name
FROM customers;

-- MySQL 8.0+
SELECT REGEXP_REPLACE(name, '[^\x20-\x7E]', '') AS clean_name
FROM customers;

-- SQL Server (no native regex, use a loop or TRANSLATE)
SELECT name AS original_name FROM customers
WHERE name NOT LIKE '%[^' + CHAR(32) + '-' + CHAR(126) + ']%';

The range 0x20 through 0x7E covers the 95 printable ASCII characters. Stripping below 32 removes control characters like tab, line feed, and carriage return, which may or may not be desirable depending on whether the field should support multiline text.

Build a character mapping table

-- PostgreSQL: Generate a complete ASCII reference table
SELECT
    g AS decimal_code,
    'x' || TO_HEX(g) AS hex_code,
    CASE WHEN g BETWEEN 32 AND 126 THEN CHR(g) ELSE '(ctrl)' END AS character
FROM generate_series(0, 127) AS g;

-- MySQL equivalent
SELECT
    seq AS decimal_code,
    HEX(seq) AS hex_code,
    CASE WHEN seq BETWEEN 32 AND 126 THEN CHAR(seq) ELSE '(ctrl)' END AS character
FROM seq_0_to_127;

This produces the same data as the ASCII table on the homepage. The base converter handles the decimal-to-hex conversion interactively.

5. Collation and Sort Order

Collation determines how strings are compared and sorted. Binary collations sort by raw byte value, which matches ASCII order: digits before uppercase before lowercase. Case-insensitive collations fold case, making 'A' and 'a' equivalent.

-- PostgreSQL: binary sort = ASCII order
SELECT * FROM (VALUES ('a'), ('A'), ('0'), ('Z'))
    AS t(c) ORDER BY c COLLATE "C";
-- Result: 0, A, Z, a

-- PostgreSQL: locale-aware sort
SELECT * FROM (VALUES ('a'), ('A'), ('0'), ('Z'))
    AS t(c) ORDER BY c COLLATE "en_US.UTF-8";
-- Result: 0, a, A, Z (case-insensitive interleaving)

The "C" or "POSIX" collation in PostgreSQL gives strict ASCII byte-order sorting. In MySQL, use _bin collations (e.g., utf8mb4_bin) for the same effect. SQL Server uses Latin1_General_BIN2.

Collation mismatches between tables cause join failures. If table_a.name uses utf8mb4_general_ci and table_b.name uses utf8mb4_bin, a JOIN on those columns raises Illegal mix of collations in MySQL. Standardize collations across tables that will be joined.

6. Encoding Conversion Between Systems

When moving data between a mainframe (EBCDIC) and a modern database (UTF-8), character codes change completely. The letter A is 193 in EBCDIC but 65 in ASCII. Most ETL tools handle this conversion, but manual SQL sometimes needs to map values explicitly.

-- PostgreSQL: Convert encoding
SELECT convert('Hello'::bytea, 'UTF8', 'LATIN1');

-- Oracle: CONVERT function
SELECT CONVERT('Hello', 'AL32UTF8', 'WE8ISO8859P1') FROM dual;

The character sets reference lists all supported encodings. The encoding comparison page shows byte-level differences between any two encodings.

7. Edge Cases and Gotchas

NULL handling

ASCII(NULL) returns NULL in all four engines. CHAR(NULL) and CHR(NULL) also return NULL. Chain with COALESCE() to provide defaults.

Multi-byte characters

ASCII('e') returns 195 in PostgreSQL with UTF-8 encoding because it returns the first byte of the UTF-8 sequence (0xC3), not the code point. This is a longstanding behavior. Use unicode('e') in newer PostgreSQL versions or ascii(convert_to('e', 'UTF8')) for the actual code point value of 233.

CHAR() in MySQL returns binary

MySQL's CHAR() returns a binary string by default. To get a character string, add USING utf8mb4: SELECT CHAR(65 USING utf8mb4);. Without it, comparisons with varchar columns may produce unexpected results because binary and character collations differ.

SQL Server CHAR() range

SQL Server's CHAR() accepts integers 0 through 255 only. For Unicode code points above 255, use NCHAR(n) instead. CHAR() returns a char(1) value using the database's default code page. NCHAR() returns an nchar(1) value using Unicode.