PostgreSQL regexp_replace with matched expression

Question

I am using PostgreSQL regexp_replace function to escape square brackets, parentheses and backslash in a string so that I could use that string as a regex pattern itself (there are other manipulations done on this string as well before using it, but they are outside the scope of this question. The idea is to replace:

[ with \[
] with \]
( with \(
) with \)
\ with \\

Postgres documentation page on regular expressions states the following:

The replacement string can contain \n, where n is 1 through 9, to indicate that the source substring matching the n'th parenthesized subexpression of the pattern should be inserted, and it can contain \& to indicate that the substring matching the entire pattern should be inserted. Write \ if you need to put a literal backslash in the replacement text.

However regexp_replace('abc [def]', '([\[\]\(\)\\])', E'\\\1', 'g'); produces abc \ def\.

Further down on that same page, an example is given, which uses \\1 notation - so I tried that.

Yet, regexp_replace('abc [def]', '([\[\]\(\)\\])', E'\\\\1', 'g'); produces abc \1def\1.

I would guess this is expected, but regexp_replace('abc [def]', '([\[\]\(\)\\])', E'.\\1', 'g'); produces abc .[def.]. That is, escaping works with characters other than the standard backslash.

At this point I don't know how to proceed. What can I do to actually give me the replacement I want?

Aleks G · Accepted Answer · 2012-08-23 10:12:30Z

5

OK, found the answer. Apparently, I need to double-escape the backslash in the replacement. Also, I need to E-prefix and double-escape backslashes in the search pattern on older versions of postgres (8.3 in my case). The final code looks like this:

regexp_replace('abc [def]', E'([\\[\\]\\(\\)\\\\\?\\|_%])', E'\\\\\\1', 'g')

Yes, it looks horrible, but it works :)

answered Aug 23, 2012 at 10:12

Aleks G

57.5k34 gold badges180 silver badges280 bronze badges

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

3 Comments

kgrittn Over a year ago

If you have standard_conforming_strings on (as is the default in recent versions) and you don't use the E prefix you eliminate a lot of backslashes, and it looks less horrible: regexp_replace('abc [def]', '([\[\]\(\)\\?\|_%])', '\\\1', 'g').

Aleks G Over a year ago

@kgrittn Yes, I appreciate it. Unfortunately, our servers are 8.3 and these were required. Without E, it would complain that E is required if escapes are used and with E it required the double-escaping.

kgrittn Over a year ago

Fair enough. For the record, you can turn on standard_conforming_strings in any version from 8.2 on, but it isn't the default until 9.0. The E prefix, while it looks uglier, has the advantage of working regardless of this setting.

guest · Accepted Answer · 2014-01-14 08:49:54Z

2

it's simpliest way

select regexp_replace('abc [def]', '([\[\]\(\)\\])', '\\\1', 'g')

edited Jan 14, 2014 at 8:49

answered Jan 14, 2014 at 8:41

guest

212 bronze badges

Collectives™ on Stack Overflow

PostgreSQL regexp_replace with matched expression

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related