PostgreSQL Regular expression remove Space

Question

Can some one please help me with this:

Select trim(regexp_replace('17 TH AV','^[0-9]+\s+TH ','\1 '))

is giving me AV.The output should be 17TH AV.

I also tried

Select trim(regexp_replace('17 TH AV','^[0-9]+\s+TH ',''))

vks · Accepted Answer · 2015-05-25 05:40:26Z

1

Select trim(regexp_replace('17 TH AV','^([0-9]+)\s+','\1'))

This should do it for you.See demo.

answered May 25, 2015 at 5:21

vks

68.1k11 gold badges96 silver badges132 bronze badges

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

Comments

Sami Kuhmonen · Accepted Answer · 2015-05-25 05:22:37Z

1

You haven't told the replace function what the \1 should refer to.

Select trim(regexp_replace('17 TH AV','^([0-9]+)\s+TH ','\1TH '))

This way you tell it with the parentheses to grab the numbers that are followed by space and TH and remove the space.

answered May 25, 2015 at 5:22

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges