CODEWITHSUNDEEP
postgresql
metalaureate
metalaureate

Reputation: 7732

Detecting special chars in postgres

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.

Upvotes: 1

Views: 211

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

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.

Upvotes: 4

Related Questions