PostgreSQL regex_replace

Question

I want to convert a string ___abc_[_]xyz to ...abc.\_xyz using regular expression.

Is it possible to convert _ and [_] into . and \_ respectively in the same query?

This is what I have done so far:

SELECT regexp_replace('___abc_[_]xyz','\[(.)\]','\\\1','g');

and

SELECT regexp_replace('___abc_[_]xyz','\[_\]','\_','g');

The Result of both queries is: ___abc_\_xyz

Since you are trying to replace with a hardcoded text, it is not possible without a callback, but PostgreSQL regexp functions do not accept callback functions as replacement arguments. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Sep 4, 2018 at 13:07

Toto · Accepted Answer · 2018-09-04 15:03:13Z

1

A douible regexp_replace can do the job:

SELECT regexp_replace(regexp_replace('___abc_[_]xyz','(?!\[)_(?!\])','.','g'),'\[_\]','\\_','g');
 regexp_replace 
----------------
 ...abc.\_xyz
(1 row)

The first one, using (?!\[)_(?!\]) will replace underscore NOT in between [ & ] with a dot.

The second will replace [_] with \_

answered Sep 4, 2018 at 15:03

Toto

91.7k63 gold badges97 silver badges135 bronze badges

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

Comments

Rémy Baron · Accepted Answer · 2018-09-04 15:04:50Z

1

You can do this with a triple replace:

SELECT replace(replace(replace('___abc_[_]xyz','[_]','***MYREPLACE***'),'_','.'),'***MYREPLACE***','\_');

you replace all [_] by a special string that can't exist in your string and not containing _ ( in my example ***MYREPLACE***)
you replace all _ by .
you replace all ***MYREPLACE*** by \_

edited Sep 4, 2018 at 15:04

user330315

answered Sep 4, 2018 at 14:31

Rémy Baron

1,4099 silver badges15 bronze badges

1 Comment

Laurenz Albe Over a year ago

I like this solution best because it is much simpler and faster than using a regular expression.

Collectives™ on Stack Overflow

PostgreSQL regex_replace

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related