Re: PostgreSQL POSIX regular expression problem

HI Greg, your solution will work for all my variations except for parentheses around the area_code.

pfdb=# select substring('OfficeName: abc office supply PhoneNumber:[commercial] (865)-999-5695, emailaddress: abc(at)office(dot)com' from '([\d\-]+)');
substring
-----------
865
(1 row)

thanks!!
________________________________________
From: Greg Sabino Mullane [greg(at)endpoint(dot)com]
Sent: Thursday, April 07, 2016 11:42 AM
To: Burgess, Freddie
Cc: pgus-general(at)postgresql(dot)org
Subject: Re: [pgus-general] PostgreSQL POSIX regular expression problem

On Wed, Apr 06, 2016 at 06:31:42PM +0000, FBurgess(at)Radiantblue(dot)com wrote:
> * Trying to build a regular expression syntax that doesn't drop the last digit (5) at the very end of the phone number.
>
> testdb=# select substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695 ' from 'PhoneNumber: \[commercial](.*?)(,|(\d\s+))');

You have two parens in there - the reason you are not getting the last digit is that the first parens (.*?) scoops
up everything - including a leading space - up to the second parens (,|(\d\s+)), which ensures that the final digit
plus whitespace combination is always excluded from the first match.

That's a very specific regex. Can you simply slurp a phone number directly, like so?:

substring('OfficeName: abc office supply PhoneNumber: [commercial] 865-999-5695 ' from '([\d\-]+)')

--
Greg Sabino Mullane greg(at)endpoint(dot)com
End Point Corporation
PGP Key: 0x14964AC8