PostgreSQL substring POSIX regular expression problem

From: "FBurgess(at)Radiantblue(dot)com" <FBurgess(at)Radiantblue(dot)com>
To: "pgus-general(at)postgresql(dot)org" <pgus-general(at)postgresql(dot)org>
Subject: PostgreSQL substring POSIX regular expression problem
Date: 2016-04-06 18:10:37
Message-ID: 3BBE635F64E28D4C899377A61DAA9FE045304FF9@NBSVR-MAIL01.radiantblue.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgus-general

* Trying to get a regular expression syntax that does not drop the last digit in the phone number

testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695 ' from 'PhoneNumber: \[commercial](.*?)(,|(\d\s+))');
substring
--------------
865-999-569
(1 row)

* Removing the space at the end returns empty, but I want the complete phone number: 865-999-5695.

testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695' from 'PhoneNumber: \[commercial](.*?)(,|(\d\s+))');
substring
-----------
(1 row)

* These two work fine

testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695 abc' from 'PhoneNumber: \[commercial](.*?)(,|(\s(\w+)$))');
substring
---------------
865-999-5695
(1 row)

testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695, abc' from 'PhoneNumber: \[commercial](.*?)(,|(\s(\w+)$))');
substring
---------------
865-999-5695
(1 row)

* but if I remove the last word, again I get null, when I need the phone number. The phone number may be the last value at the end of the string or it may not be.

testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695' from 'PhoneNumber: \[commercial](.*?)(,|(\s(\w+)$))');
substring
-----------
(1 row)

Thanks

Browse pgus-general by date

  From Date Subject
Next Message FBurgess@Radiantblue.com 2016-04-06 18:31:42 PostgreSQL POSIX regular expression problem
Previous Message Jonathan S. Katz 2014-12-04 23:06:53 Re: PostgreSQL 9.3.4/PostGIS 2.1.1 function