regexp_replace in postgres keep only desired with single space

Question

What i'd like is to turn a string like this;

10 - 15 st. pan,cras

to

10 - 15 ST PANCRAS

I get most of the way with

 `select upper(regexp_replace('10  -  15 st.  pan,cras', '[^a-zA-Z 0-9-]', '', 'g'));`

But i can't seem to remove the double spaces around the number. I've tried, adding an extra space in the expression;

 `select upper(regexp_replace('10  -  15 st.  pan,cras', '[^a-zA-Z  0-9-]', '', 'g'));`

But no difference in outcome. I'm using regexp_replace as i find the substring syntax harder to follow. On 9.6 string is stored in text

Vao Tsun · Accepted Answer · 2017-08-03 12:11:51Z

1

you can add space collapsing to only one expression, like this:

t=# select regexp_replace('q            q','( ){1,}',' ','g');
 regexp_replace
----------------
 q q
(1 row)

It replaces with single space if finds one or more following spaces in a row.

so in your case will be

t=# select regexp_replace(upper(regexp_replace('10  -  15 st.  pan,cras', '[^a-zA-Z 0-9-]', '', 'g')),'( ){1,}',' ','g');
   regexp_replace
--------------------
 10 - 15 ST PANCRAS
(1 row)

answered Aug 3, 2017 at 12:11

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Vao Tsun Over a year ago

yes, a short instructions are in postgresql.org/docs/current/static/functions-matching.html

Collectives™ on Stack Overflow

regexp_replace in postgres keep only desired with single space

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related