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!

Andrew Cheong · Accepted Answer · 2013-10-24 08:59:40Z

32

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]+?.

answered Oct 24, 2013 at 8:59

Andrew Cheong

30.4k17 gold badges103 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alexius · Accepted Answer · 2013-10-24 08:56:14Z

8

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

answered Oct 24, 2013 at 8:56

alexius

2,58620 silver badges24 bronze badges

Comments

Rob McDonell · Accepted Answer · 2014-10-05 06:56:36Z

8

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.

answered Oct 5, 2014 at 6:56

Rob McDonell

1,32910 silver badges15 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:33:15Z

4

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, 2017 at 11:33

CommunityBot

11 silver badge

answered Dec 4, 2013 at 11:50

sal

1,2492 gold badges17 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related