How to remove Non-English characters in Postgresql

Question

I have been trying to remove rows that have non-english words in Postgresql. However, it does not seem working. This is my current code and sample data:

Select
translationid,
medium
from translation
where medium like '%[^A-Z,a-z]%'

Those *"English"* characters are in fact Latin – Cid Sep 01 '21 at 13:05 — Cid, Sep 01 '21 at 13:05
how do i extract it in postgresql? – Sriram Sep 01 '21 at 13:05 — Sriram, Sep 01 '21 at 13:05

Laurenz Albe · Accepted Answer · 2021-09-01T13:48:12.600

1

You need a regular expression match with the ~ operator:

WHERE NOT meduim ~ '[^A-Za-z]'

You might want to add other characters like space or - to the list in the brackets.

edited Sep 01 '21 at 13:48

answered Sep 01 '21 at 13:06

Laurenz Albe

209,280
17
206
263

i still can see the non-english characters – Sriram Sep 01 '21 at 13:09
If you want to *exclude* all entries with non-english characters, add a `NOT` - I'll update the answer. – Laurenz Albe Sep 01 '21 at 13:48

How to remove Non-English characters in Postgresql

1 Answers1