CODEWITHSUNDEEP
regexpostgresqlregexp-replace
Tom H
Tom H

Reputation: 207

How to use regexp_replace to replace only the capturing group and not the full matching string

We have a column in the database containing values similar to '150 W 96 Th St' and '4514 282 Nd St'. As you may have already noticed, there is a space between the numeric street name and the suffix, i.e '96 TH'.

I am trying to write a postgresql query that will remove that space. Using the following:

SELECT regexp_matches('150 W 96 Th St', ' \d+( )(?:St|Nd|Rd|Th) ')

I get:

{" "}

Indicating, that only the capturing group is returned.

However, when I run 

SELECT regexp_replace('150 W 96 Th St', ' \d+( )(?:St|Nd|Rd|Th) ', '')

I get

150 WSt

where the whole matching string is replaced.

I have also tried

SELECT regexp_replace('150 W 96 Th St', ' \d+( )(?:St|Nd|Rd|Th) ', '\1')

which I thought would only replace the first capture group.

What else should I try?

Upvotes: 2

Views: 4451

Answers (1)

castletheperson
castletheperson

Reputation: 33506

Put the capture groups around the things you want to keep, and then reference them in the replacement string.

SELECT regexp_replace('150 W 96 Th St', '(\d+) (St|Nd|Rd|Th)', '\1\2')

Upvotes: 10

Related Questions