to_ascii()

A system function for converting a string from another encoding

to_ascii() is a system function for converting a string from a small range of other encodings.

to_ascii() was added in PostgreSQL 7.1.

Usage

to_ascii(string TEXT)
to_ascii(string TEXT, encoding NAME)
to_ascii(string TEXT, encoding INTEGER)

In practice, the first form, which defaults to the current database encoding, is the only variant of practical use.

Conversions from the following encodings are supported:

Encoding Number
LATIN1 8
LATIN2 9
LATIN9 16
WIN1250 29

The PostgreSQL documentation does not specify the encoding numbers noted in the preceding table; these correspond to the matching values in the pg_enc enum defined in src/include/mb/pg_wchar.h.

The contrib module unaccent provides a more flexible range of options to achieve the same objective.

Change history

Examples

Basic usage:

asciitest=# SELECT to_ascii('überbewertete äthiopische Ödnis');
            to_ascii             
---------------------------------
 uberbewertete athiopische Odnis
(1 row)

The same result using the encoding name and number respectively:

asciitest=# SELECT to_ascii('überbewertete äthiopische Ödnis', 'LATIN1');
            to_ascii             
---------------------------------
 uberbewertete athiopische Odnis
(1 row)

asciitest=# SELECT to_ascii('überbewertete äthiopische Ödnis', 8);
            to_ascii             
---------------------------------
 uberbewertete athiopische Odnis
(1 row)

Attempting to convert from unsupported or invalid encodings:

asciitest=# SELECT to_ascii('ü', 'EUC_JP');
ERROR:  encoding conversion from EUC_JP to ASCII not supported

asciitest=# SELECT to_ascii('ü', 19);
ERROR:  encoding conversion from WIN1258 to ASCII not supported

asciitest=# SELECT to_ascii('ü', 99);
ERROR:  99 is not a valid encoding code

to_ascii() produces garbage output when executed in a database not in one of the supported encodings:

postgres=# \l postgres
                               List of databases
   Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
----------+----------+----------+-------------+-------------+-------------------
 postgres | postgres | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | 
(1 row)

postgres=# SELECT to_ascii('äü');
 to_ascii 
----------
 A A 
(1 row)

Categories

Locale and character set handling, System function

See also

unaccent