Hi, in PG 8.3 I have the following issue:

db_atm=# select substring('1.3.6.1.4.1.9.1.182',
E'(1\.3\.6\.1\.4\.1\.9\.1\.\d*)', 'g');
substring
-----------

(1 row)

db_atm=# select substring('1.3.6.1.4.1.9.1.182',
E'(1\.3\.6\.1\.4\.1\.9\.1\.[^\.]*)', 'g');
substring
---------------------
1.3.6.1.4.1.9.1.182
(1 row)

why \d it is (it seems) not recognised? what mistake am I making?

Thanks,

Anselmo

On Fri, 22 Jan 2010 11:54:05 +0100, Anselmo Canfora wrote:

Anselmo, you need to double your escape like this:
postgres=# select substring('1.3.6.1.4.1.9.1.182',
E'(1.3.6.1.4.1.9.1.\\d+)');

substring
---------------------
1.3.6.1.4.1.9.1.182
(1 row)

postgres=#


That escape doubling is annoying but is documented.
--
http://mgogala.freehostia.com

Il 22/01/2010 14.38, Mladen Gogala ha scritto:
I noticed that, it is that when I try to double escape to dots too (I
want the literal dot, not the any-char wildcard) it does not work.

On Fri, 22 Jan 2010 17:36:42 +0100, Anselmo Canfora wrote:


Hmmm, I tried your example without the ending 'g' and it works. What is
that, anyway?

scott=> select substring('1.3.6.1.4.1.9.1.182',
E'(1\.3\.6\.1\.4\.1\.9\.1\.\\d*)');
substring
---------------------
1.3.6.1.4.1.9.1.182
(1 row)

scott=>




--
http://mgogala.byethost5.com

Il 22/01/2010 21.15, Mladen Gogala ha scritto:
'g' is a regex modification that should stand for "greedy". In Perl
regexps this one instructs the parser to extract as many atoms as many
match the expression, instead of stopping on first one.
In your example one should use double backslashes for dots too, anyway
you found the culprit, actually removing 'g' works:

db_atm=# select substring('1.3.6.1.4.1.9.1.182',
E'(1\\.3\\.6\\.1\\.4\\.1\\.\\d+)', 'g');
substring
-----------

(1 row)

db_atm=# select substring('1.3.6.1.4.1.9.1.182',
E'(1\\.3\\.6\\.1\\.4\\.1\\.\\d+)');
substring
---------------
1.3.6.1.4.1.9
(1 row)

this one seems to me a behavior not adherent to regexp "standards".
Thank you very much for the help.

On 2010-01-22, Anselmo Canfora <spam (AT) mm (DOT) st> wrote:
I cannot find PG documentation for a form of substring taking three text
arguments separated by comma.

however, /df+ gives me:

Schema | pg_catalog
Name | substring
Result data type | text
Argument data types | text, text, text
Volatility | immutable
Owner | postgres
Language | sql
Source code | select pg_catalog.substring($1, pg_catalog.similar_escape($2, $3))
Description | extracts text matching SQL99 regular expression

it seems to be a SQL99 compatibility function, possibly an alias for

substring(string from pattern for escape)

yup, after minimal testing that seems to be the one.


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Il 24/01/2010 10.24, Jasen Betts ha scritto:
My fault, it is actually so. I had a too much rapid survey on docs.