PostgreSQL regexp_replace trouble ignoring preceded by

Question

am trying the following regex_replace statement in PostgreSQL (9.2)

 # SELECT regexp_replace('_this._is_a_long_entry._of.nonsense_text', '\._|^_', '$','g');

Objective:

is to replace all underscore characters preceded by a period with '$'. Am also replacing the underscore anchored at position 1; this is working. I'd like to cram both matches into the one statement. All of this is to develop a mechanism for pushing some weird text we have into a PostreSQL ltree structure.

Issue:

How to make the first replacement above work, without molesting the period character?

I'd like to see the result look like this:

$this.$is_a_long_entry.$of.nonsense_text

Note: Have also tried explicit capture of the underscore, but the PG implementation seems to ignore this:

# SELECT regexp_replace('_this.is_long_entry._of.nonsense_text', '\.(_)|^_', '$','g');

@m.buettner: I have tried that, yes - though I think the or syntax must compare full expressions; I don't know that regex can swap like that. In any case, PostgreSQL hiccups with: ERROR: invalid regular expression: quantifier operand invalid. Thanks for the swing, though. — DrLou
– DrLou, Commented Sep 26, 2012 at 19:39
the problem might be the ? then but alternation (|) does generally work in subpatterns in regex. how about \.(_)|^(_)? — Martin Ender
– Martin Ender, Commented Sep 26, 2012 at 19:43
@m.buettner: (Your logic is subtly different there, though - in that the match object doesn't span the alternation - In any case, yes; I've tried this variant, too. This works... partly) It substitutes, but doesn't leave the period char in place. — DrLou
– DrLou, Commented Sep 26, 2012 at 19:56

Erwin Brandstetter · Accepted Answer · 2012-09-26 19:59:55Z

2

SELECT regexp_replace('_this._is_a_long_entry._of.nonsense_text'
                     ,'(\.|^)_' -- capture the preceding char (none at start)
                     , '\1$'    -- use the captured char
                     ,'g');     -- globally

This is assuming standard_conforming_strings = ON.

answered Sep 26, 2012 at 19:59

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

DrLou Over a year ago

Erwin! Great! That worked! I had tried a variant of PostgreSQL's \1 referencing earlier - clearly got it wrong. Many thanks for the answer. That alternation within the subpattern is a new one for me; tks to @m.buettner and you for that. (standard_conforming_strings = ON by default in 9.2, I believe)

Erwin Brandstetter Over a year ago

@DrLou: Cool. :) You're right about standard_conforming_strings. It's on by default since 9.1. But it's just a default ...

Collectives™ on Stack Overflow

PostgreSQL regexp_replace trouble ignoring preceded by

Objective:

Issue:

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Objective:

Issue:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related