Extract specific alphabetic characters using SQL query

Question

Background: I am using PostgreSQL 11 on CentOS. I have a database having UTF-8 encoding, in specific tables I have some Latin characters as follows [ÄéÒçòý]

Objective: I want to replace those with standard English characters for that I am trying to use the following query to list down those specific characters but getting all characters set :

SELECT name_0 from public.gadm36_0_iso2_iso3 where name_0 ~ '[[:alpha:]]'; 

I am searching on my side and referring to some posts as well.

Any help/any suggestions would be great !!!

Answer 1

Perhaps you are looking for unaccent:

CREATE EXTENSION unaccent;

SELECT unaccent('ÄéÒçòý');

 unaccent 
----------
 AeOcoy
(1 row)

Answer 2

You can use regular expressions for this

SELECT 'ÄéÒçòý' ~* '\A[A-Z0-9]*\Z';

The output should be false for the above SQL statement. So use this in your where clause.