1

Related to a comment, I would like to find a way to clean with regexp_replace in PostgreSQL any non-digit except the + at beginning of the string.

For example +(7)9125415501 shall become +79125415501

1
  • Have you considered the TRANSLATE() function?
    – Vérace
    Sep 3, 2023 at 20:19

1 Answer 1

1

You can anchor the first character to be replaced to the begin of the string and search for everything not 0 - 9 or + and assert with a look behind, that any other replacement is preceded by any character, hence it's not the begin of the string.

regexp_replace('+(7)9125415501', '^[^0-9\+]|(?<=.)[^0-9]', '', 'g')

Look behinds were introduced in version 9.6. If you're on anything lower than that you can only use look aheads. You can do so reversing the string and instead of looking behind and anchoring for the beginning look ahead and anchor for the end. Rereverse it in the end.

reverse(regexp_replace(reverse('+(7)9125415501'), '[^0-9](?=.)|[^0-9\+]$', '', 'g'))

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.