TO_ASCII

Transliterate non-ASCII characters to their closest ASCII equivalents.

Category: stringReturns: VARCHARDialect: PostgreSql

Syntax

TO_ASCII(str)

Description

## Overview Transliterates non-ASCII characters in the input string to their closest ASCII equivalents. Accented Latin letters have their diacritics stripped (for example 'é' becomes 'e'). Ligatures are decomposed to their component letters ('æ' becomes 'ae'). Characters that have no reasonable ASCII approximation are removed or replaced with a placeholder, depending on the engine build. TO_ASCII is useful for building accent-folded search keys, normalising names for fuzzy matching, and for legacy systems that require ASCII-only output. For strictly lossless Unicode normalisation, use NORMALIZE instead. ## Behavior - Returns NULL when the input is NULL. - Returns the input unchanged when it already consists entirely of ASCII (code points 0-127). - Latin accented characters have their diacritical marks removed. - Ligatures and compatibility characters may be decomposed into multiple ASCII characters. - Non-Latin scripts (CJK, Cyrillic, Arabic, etc.) have no universal ASCII mapping; they are typically removed or replaced with '?'. The exact behaviour is implementation-defined. - Output is guaranteed to contain only code points in [0, 127]. ## Compatibility - Matches the common SQL TO_ASCII semantics for the default encoding. - The specific substitution table for exotic characters may vary between engine builds; do not rely on exact output for non-Latin scripts.

Parameters

Name	Type	Description
`str`		Specifies the input string containing characters to transliterate to ASCII.

Examples

-- Transliterate accented Latin characters
SELECT TO_ASCII('café');  -- 'cafe'

-- German umlauts
SELECT TO_ASCII('München');  -- 'Munchen'

-- Already ASCII is unchanged
SELECT TO_ASCII('hello');  -- 'hello'

-- Mixed accented content
SELECT TO_ASCII('résumé');  -- 'resume'

-- NULL propagates
SELECT TO_ASCII(CAST(NULL AS VARCHAR));  -- NULL

-- Produce an ASCII-only search key for fuzzy name matching
SELECT customer_id, LOWER(TO_ASCII(last_name)) AS last_name_key
FROM retail.customers.profiles;

Pitfalls

TO_ASCII is lossy. 'Zürich' becomes 'Zurich' and you cannot recover the umlaut. Keep the original column; derive an ASCII shadow column for search only.
Non-Latin scripts have no meaningful ASCII mapping. CJK, Cyrillic, and similar text may be stripped entirely, leaving rows with empty keys.
The transliteration is not locale-aware. 'ß' is usually mapped to 's' or 'ss' depending on build; do not assume one convention.
TO_ASCII is not a substitute for Unicode normalisation. Use NORMALIZE NFC to compose combining sequences before comparing; use TO_ASCII only when you genuinely need ASCII-only output.