Detecting special chars in postgres

Question

I have usernames in my postgres 9 db such as

Ron R ty ♥☆♡★Green Eyes♥☆♡★
Sωℯℯт۞Angel 2 ᾧ➍ᾧ ty Լù☪ƖƒεƦ

the db is encoded in utf-8

is there a way to detect the presence of these special chars outside standard roman chars in SQL?

I tried using convert documented here http://www.postgresql.org/docs/9.1/static/functions-string.html but only got errors.

Answer 1

Try matching on a regexp character range based on unicode code-point.

WHERE uname ~ '[\x80-\xffff]';

Or, if you want to be more strict you can exclude anything non-alphanumeric.

WHERE uname ~ '[^[:alnum:]]

Other character-classes are available too. See the docs for details.