Split an address string but keep numbers within, in PostgreSQL with regex

Question

We have the next SQL query in Postgresql 9.3


SELECT 
  regexp_split_to_array('merchant - mall', '(?!^)\s*(?!\d)[\-]\s*(?!\d)\s*(?=.)') as text1,
  regexp_split_to_array('merchant - street 245', '(?!^)\s*(?!\d)[\-]\s*(?!\d)\s*(?=.)') as text2,
  regexp_split_to_array('merchant - street-245', '(?!^)\s*(?!\d)[\-]\s*(?!\d)\s*(?=.)') as text3,
  regexp_split_to_array('merchant - street - 245', '(?!^)\s*(?!\d)[\-]\s*(?!\d)\s*(?=.)') as text4

The result is:

"{merchant,mall}","{merchant,street 245}","{merchant,street-245}","{merchant,street,245}"

The problem is that 4th sample considers the number as a separate string. Is there any way of doing this with regex in postgresql?

The regex used (in the query) is:

(?!^)\s*(?!\d)[\-]\s*(?!\d)\s*(?=.)

karthik manchala · Accepted Answer · 2015-05-23 11:44:18Z

2

Just add \s* in your negative lookahead assertion (?!\d):

(?!^)\s*(?<!\d)[\-]\s*(?!\s*\d)\s*(?=.)
          ^              ^^

Or you can use the following simplified regex:

(?<!\d)\b[ -]+\b(?!\d)

See DEMO

edited May 23, 2015 at 11:44

answered May 22, 2015 at 17:01

karthik manchala

13.7k1 gold badge34 silver badges55 bronze badges

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

7 Comments

Manuel Alvarez Over a year ago

Great! And how would it be if original string will be 123-merchant?

karthik manchala Over a year ago

It will be {123,merchant} what are you expecting?

Manuel Alvarez Over a year ago

As in 3rd and 4th cases, I expect it to stay with text: { 123-merchant}

Manuel Alvarez Over a year ago

Yes, it works properly in Python, but not in postgres where it seems that you can't use lookbehind assertions.

karthik manchala Over a year ago

ohh ok... and you can use the above simplified regex inplace of previous one

|

Collectives™ on Stack Overflow

Split an address string but keep numbers within, in PostgreSQL with regex

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related