Thread: substring regular expression

Dear Colleague

This works (returns 12345):
select substring('ABC 12345 XYZ' from '%#"[0-9][0-9][0-9][0-9][0-9]#"%' for '#') as found_5_digits;

While this fails (returns blank):
select substring('ABC 12345 XYZ' from '%#"[0-9]{5}#"%' for '#') as no_5_digits;

Am I misunderstanding the documentation on using [0-9]{5} or do you think it's a bug?

TIA

--------------------------------------------------------------------------------------------------------------------------------------------------------------
Barry Brunning | Data Stream Pty Limited | p/f. +612 9982 3550 | w. http://www.datastream.com.au

On Thu, Sep 26, 2013 at 11:35 PM, Barry Brunning
<barry.brunning@datastream.com.au> wrote:
> This works (returns 12345):
> select substring('ABC 12345 XYZ' from '%#"[0-9][0-9][0-9][0-9][0-9]#"%' for '#') as found_5_digits;
>
> While this fails (returns blank):
> select substring('ABC 12345 XYZ' from '%#"[0-9]{5}#"%' for '#') as no_5_digits;
>
> Am I misunderstanding the documentation on using [0-9]{5} or do you think it's a bug?

The "for escape" version of substring() supports SQL regexp syntax
only, that doesn't have {N} construction. The "non for escape" version
supports POSIX regexp syntax, that does have it:

select substring('ABC 12345 XYZ' from ' \d{5} ');

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com