0

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?

Improve this question
3
  • "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.
    – Erwin Brandstetter
    Aug 31 at 23:36
  • Acceptable characters in our character set: alphanumeric, some accented characters (e.g., ñ and Ñ), apostrophe, dot and comma.
    – Bona Rae Villarta
    Sep 1 at 5:18
  • Still no clear definition.
    – Erwin Brandstetter
    Sep 1 at 9:54

0

Reset to default

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.