I am cleaning up / priming our PostgreSQL 12 database for future data-related activities (e.g. data encryption). I have tried the following methods to delete non-basic Latin / basic accented Latin / punctuational values from one of our tables:

  • regexp_replace(field, '[^[:graph:]]', '', 'g') and SIMILAR TO '%[^[:graph:]]%' (in our implementation, [:print:] is not working in regexp_replace)
  • btrim(field, '') - not working for Unicode character-containing strings but only Latin strings.

However, the extraneous Unicode characters, even those embedded within acceptable (i.e., Latin and punctuational) values (e.g. \ud83d) do not get filtered / deleted from the values, hence, I couldn't prime the data.

What can I do to filter out only unacceptable Unicode values in regexp_replace and delete them, retaining only acceptable characters?

  • "non-basic Latin / basic accented Latin / punctuational values" is unclear to me. Does not seem to match either [:graph:] or [:print:] character classes. Please define acceptable characters exactly. The underlying OS and LC_CTYPE also influence character classes beyond the the 7-bit ASCII set. Aug 31 at 23:36
  • Acceptable characters in our character set: alphanumeric, some accented characters (e.g., ñ and Ñ), apostrophe, dot and comma. Sep 1 at 5:18
  • Still no clear definition. Sep 1 at 9:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.