postgresql regex replaces the first match only

Question

Struggling to understand how to make regexp_match string function in postgresql behave the way I want. I have a string and I want to replace any instance of multiple spaces with just one space. So for instance

'mitt     romney'

becomes

'mitt romney'

I believe the following code should work:

SELECT regexp_replace('This      is a    test', '[ ]+', ' ');

The expected result would be

'This is a test'`

but instead I get back

'This is a     test'`.

Its as if the replacement is only replacing the first match. Anyone know how to solve this?

Thanks

JayC · Accepted Answer · 2012-11-07 04:48:40Z

8

I don't think it's bizarre, looks to me like it's documented: http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag i specifies case-insensitive matching, while flag g specifies replacement of each matching substring rather than only the first one.

Emphasis, my own.

answered Nov 7, 2012 at 4:48

JayC

7,1612 gold badges30 silver badges42 bronze badges

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

Comments

Evan V · Accepted Answer · 2012-11-07 04:51:26Z

7

There is a fourth optional parameter in the regexp_replace function which, when specified, resuls in the desired behavior.

If I do

regexp_replace('This      is    a        test`, '[ ]+', ' ', 'g')

I get the desired result.

answered Nov 7, 2012 at 4:51

Evan V

1,8871 gold badge12 silver badges10 bronze badges

Collectives™ on Stack Overflow

postgresql regex replaces the first match only

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related