Using postgres regexp_replace to replace a list of strings by always the same string

Question

Hi i would like to use regexp_replace (or any other postgres function if reasonable) to replace characters in a string by a '', i.e. erase them.

Using regexp_replace(('def4abcdef4ab','4', '','g') i can replace/erase all occurrences of '4', but i want to also replace all occurences of 'e' and b' in the same process, without using several nested processes like regexp_replace(regexp_replace(regexp_replace())).

i.e. i want to be able to provide a short list of different strings which then should be replace by a unique string.

Any ideas? Thanks a lot in advance!

score 32 · Answer 1 · answered Oct 24 '13 at 08:59

The canonical way is to use character classes, like so,

regexp_replace('def4abcdef4ab','[4eb]', '','g')

though @alexius's method can also handle strings.

Not sure if perhaps non-greedily quantifying the expression would make it more efficient, e.g. [4eb]+?.

score 8 · Answer 2 · answered Oct 24 '13 at 08:56

8

regexp_replace('def4abcdef4ab','4|e|b', '','g')

answered Oct 24 '13 at 08:56

alexius

2,501
20
21

score 8 · Answer 3 · answered Oct 05 '14 at 06:56

You don't need regular expressions for single character replacement Use the translate function, ie

translate('def4abcdef4ab','4eb', '')

If your strings are multiple characters long, then use replace(). Even nested, this would likely give better performance than regular expressions.

score 3 · Accepted Answer · edited May 23 '17 at 11:33

3

ok found out the answer myself (helped by PostgreSQL various clean up of string \ varchar):

select regexp_replace(
E'aśx€ ąsd, dsa w|adwf\n  YZas dw dgaa[aw] asdd',
'[^a-zA-Y0-9 ]',
'',
'g');

edited May 23 '17 at 11:33

Community

1
1

answered Dec 04 '13 at 11:50

sal

1,199
1
13
31

Using postgres regexp_replace to replace a list of strings by always the same string

4 Answers4