Convert Letters to Numbers in SQL — Oracle, MySQL, PostgreSQL

SQL provides built-in functions for converting letters to their numeric ASCII values across all major database systems. Whether you are cleaning data, building search features, or implementing encoding logic directly in your queries, understanding how ASCII() and CHAR()/CHR() work is essential. For a quick visual reference, try our letters to numbers converter tool.

The ASCII() Function — Universal Across Databases

Every major SQL database supports the ASCII() function, which returns the numeric ASCII code of the first character in a string.

-- Works in MySQL, PostgreSQL, SQL Server, and Oracle
SELECT ASCII('A');    -- Returns 65
SELECT ASCII('Z');    -- Returns 90
SELECT ASCII('a');    -- Returns 97
SELECT ASCII('z');    -- Returns 122

-- For alphabet position (A=1), subtract 64
SELECT ASCII('A') - 64 AS position;  -- Returns 1
SELECT ASCII('M') - 64 AS position;  -- Returns 13
SELECT ASCII('Z') - 64 AS position;  -- Returns 26

The ASCII() function only processes the first character. For multi-character strings, ASCII('Hello') returns 72 (the code for H). See all ASCII values on our ASCII converter page.

Reverse Conversion: Number to Letter

The reverse function has different names depending on the database:

-- MySQL / SQL Server
SELECT CHAR(65);      -- Returns 'A'
SELECT CHAR(90);      -- Returns 'Z'
SELECT CHAR(13 + 64); -- Returns 'M' (position 13)

-- Oracle / PostgreSQL
SELECT CHR(65);       -- Returns 'A'
SELECT CHR(90);       -- Returns 'Z'
SELECT CHR(13 + 64);  -- Returns 'M'

Per-Database Syntax Comparison

Converting Column Data: Single Character

-- Convert the first letter of each name to its position
-- Works in all databases (adjust SUBSTRING for Oracle)
SELECT
    name,
    ASCII(UPPER(SUBSTRING(name, 1, 1))) - 64 AS first_letter_position
FROM customers
ORDER BY first_letter_position;

-- Example output:
-- | name    | first_letter_position |
-- |---------|-----------------------|
-- | Alice   | 1                     |
-- | Bob     | 2                     |
-- | Charlie | 3                     |

Processing Entire Strings in MySQL

MySQL does not have a native "map each character" function, so we use a recursive CTE or a helper approach:

-- MySQL 8.0+ recursive CTE approach
WITH RECURSIVE chars AS (
    SELECT
        'HELLO' AS original,
        1 AS pos,
        ASCII(UPPER(SUBSTRING('HELLO', 1, 1))) - 64 AS letter_pos
    UNION ALL
    SELECT
        original,
        pos + 1,
        ASCII(UPPER(SUBSTRING(original, pos + 1, 1))) - 64
    FROM chars
    WHERE pos < CHAR_LENGTH(original)
)
SELECT
    original,
    GROUP_CONCAT(letter_pos ORDER BY pos SEPARATOR '-') AS encoded
FROM chars
WHERE letter_pos BETWEEN 1 AND 26;
-- Result: HELLO → 8-5-12-12-15

Processing Entire Strings in PostgreSQL

PostgreSQL offers regexp_split_to_table() for elegant character-by-character processing:

-- PostgreSQL: Convert each character
SELECT string_agg(
    (ASCII(UPPER(ch)) - 64)::text, '-' ORDER BY ord
)
FROM (
    SELECT ch, row_number() OVER () AS ord
    FROM regexp_split_to_table('HELLO', '') AS ch
) sub
WHERE ASCII(UPPER(ch)) BETWEEN 65 AND 90;
-- Result: 8-5-12-12-15

-- PostgreSQL function for reuse
CREATE OR REPLACE FUNCTION encode_a1z26(input_text TEXT)
RETURNS TEXT AS $$
    SELECT string_agg(
        (ASCII(UPPER(ch)) - 64)::text, '-' ORDER BY ord
    )
    FROM (
        SELECT ch, row_number() OVER () AS ord
        FROM regexp_split_to_table(input_text, '') AS ch
    ) sub
    WHERE ASCII(UPPER(ch)) BETWEEN 65 AND 90;
$$ LANGUAGE SQL IMMUTABLE;

-- Usage
SELECT encode_a1z26('Hello World');
-- Result: 8-5-12-12-15-23-15-18-12-4

Processing Entire Strings in Oracle

-- Oracle: CONNECT BY LEVEL for character iteration
SELECT LISTAGG(
    ASCII(UPPER(SUBSTR('HELLO', LEVEL, 1))) - 64, '-'
) WITHIN GROUP (ORDER BY LEVEL) AS encoded
FROM dual
CONNECT BY LEVEL <= LENGTH('HELLO')
AND ASCII(UPPER(SUBSTR('HELLO', LEVEL, 1)))
    BETWEEN 65 AND 90;
-- Result: 8-5-12-12-15

Practical Use Case: Data Cleaning

A common real-world use is cleaning mixed alphanumeric data. For instance, extracting only the letter components and converting them:

-- MySQL: Extract letters from a product code and get positions
SELECT
    product_code,
    -- Remove digits, keep letters
    REGEXP_REPLACE(product_code, '[^A-Za-z]', '') AS letters_only,
    -- Position of first letter
    ASCII(UPPER(SUBSTRING(
        REGEXP_REPLACE(product_code, '[^A-Za-z]', ''), 1, 1
    ))) - 64 AS first_letter_pos
FROM products;

-- PostgreSQL: Same concept
SELECT
    product_code,
    regexp_replace(product_code, '[^A-Za-z]', '', 'g') AS letters_only
FROM products;

Building a Lookup Table

For frequent conversions, a permanent lookup table is more efficient than calculating on every query. You can see all 26 mappings on our ASCII table page.

-- Create a reference table
CREATE TABLE alphabet_lookup (
    letter     CHAR(1) PRIMARY KEY,
    position   INT NOT NULL,
    ascii_code INT NOT NULL
);

-- Populate it (PostgreSQL / MySQL)
INSERT INTO alphabet_lookup (letter, position, ascii_code)
SELECT
    CHR(i) AS letter,
    i - 64 AS position,
    i AS ascii_code
FROM generate_series(65, 90) AS s(i);  -- PostgreSQL

-- MySQL equivalent
INSERT INTO alphabet_lookup (letter, position, ascii_code)
WITH RECURSIVE nums AS (
    SELECT 65 AS i
    UNION ALL
    SELECT i + 1 FROM nums WHERE i < 90
)
SELECT CHAR(i), i - 64, i FROM nums;

-- Now use JOINs for fast lookups
SELECT al.position
FROM alphabet_lookup al
WHERE al.letter = 'M';  -- Returns 13

SQL Server Specific: T-SQL Function

-- SQL Server: Create a scalar function
CREATE FUNCTION dbo.EncodeA1Z26 (@text NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    DECLARE @i INT = 1;
    DECLARE @c CHAR(1);
    DECLARE @pos INT;

    WHILE @i <= LEN(@text) BEGIN
        SET @c = UPPER(SUBSTRING(@text, @i, 1));
        SET @pos = ASCII(@c) - 64;
        IF @pos BETWEEN 1 AND 26 BEGIN
            IF LEN(@result) > 0 SET @result = @result + '-';
            SET @result = @result + CAST(@pos AS VARCHAR);
        END
        SET @i = @i + 1;
    END

    RETURN @result;
END;

-- Usage
SELECT dbo.EncodeA1Z26('Hello');  -- '8-5-12-12-15'