Postgres split word with number and letters and amel case into two columns

Question

I have a bunch of addresses that I need to split where the street number meets the city, and where its camelcase , ie:

255 S Broad St Ste 1600Goldberg Miller & Rubin PcPhiladelphia, PA, 19107-4534

You can see the number/string word is "1600Goldberg". And the CamelCase word is "PcPhiladelphia". Is there any expression to add a space?

The new cell should read:

121 S Broad St Ste 1600 Goldberg Miller & Rubin Pc Philadelphia, PA, 19107-4534

It should split all CamelCase and alphanumeric words with a space.

I figured out the camel case part: update table set address = regexp_replace(address, '([a-z])([A-Z])', '\1 \2','g') — superdee, May 22 '19 at 00:36
I think the second part is something like this, but I need a space, instead of replacing it: select address, regexp_replace(address, '(?=\w*[0-9])(?=\w*[a-z])\w+', '\1 \2', 'g') from table; — superdee, May 22 '19 at 00:39

score 0 · Answer 1 · answered May 22 '19 at 00:43

Search for a digit or a lower case letter followed by an upper case letter.

SELECT regexp_replace('255 S Broad St Ste 1600Goldberg Miller & Rubin PcPhiladelphia, PA, 19107-4534', '([\da-z])([A-Z])', '\1 \2', 'g');

That gets you:

| regexp_replace                                                                  |
| :------------------------------------------------------------------------------ |
| 255 S Broad St Ste 1600 Goldberg Miller & Rubin Pc Philadelphia, PA, 19107-4534 |

db<>fiddle

Postgres split word with number and letters and amel case into two columns

1 Answers1