CODEWITHSUNDEEP
sqlregexpostgresql
faya
faya

Reputation: 5715

Regex remove all occurrences of multiple characters in a string

In my PostgreSQL I want to replace all characters (;<>) occurrences in a string.

My query:

update table_name set text = regexp_replace(text, '/[(;<>)]+/g', '');

I think my regexp is wrong. Can anyone help me out with it?

Upvotes: 2

Views: 4421

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Use the much faster translate() for this simple case:

UPDATE tbl SET text = translate(text, '(;<>)', '');

Every character in the second parameter that has no counterpart in the third parameter is replaced with nothing.

The regular expression solution could look like this:

regexp_replace(text, '[(;<>)]', '', 'g');

Essential element is the 4th parameter 'g' to replace "globally" instead of just the first match. The second parameter is a character class.
You were on the right track, just a matter of syntax for regexp_replace().

Hint on UPDATE

If you don't expect all rows to be changed, I would strongly advise to adapt your UPDATE statement:

UPDATE tbl
SET    text =  translate(text, '(;<>)', '')
WHERE  text <> translate(text, '(;<>)', '');

This way you avoid (expensive) empty updates. (NULL is covered automatically in this particular case.)

Upvotes: 7

Related Questions