REGEXP_SUBSTR() is always NULL
"Álvaro G. Vicario"
REGEXP_SUBSTR() always returns NULL. E.g.:
SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
FROM DUAL
In my local 10g XE, however, it returns 1234 as expected.
Is it me or is it the server?
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
ddf
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 server,
> REGEXP_SUBSTR() always returns NULL. E.g.:
>
> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
> FROM DUAL
>
> In my local 10g XE, however, it returns 1234 as expected.
>
> Is it me or is it the server?
>
> --
> -- Mi sitio sobre programación web:http://borrame.com
It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
10.1.0.2 release. You're certain you've posted the version for the
Enterprise Edition correctly?
David Fitzjarrell
"Álvaro G. Vicario"
> On Jun 25, 6:29 am, "�lvaro G. Vicario"
> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 server,
>> REGEXP_SUBSTR() always returns NULL. E.g.:
>>
>> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
>> FROM DUAL
>>
>> In my local 10g XE, however, it returns 1234 as expected.
>>
>> Is it me or is it the server?
> It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
> 10.1.0.2 release. You're certain you've posted the version for the
> Enterprise Edition correctly?
Absolutely: SELECT BANNER FROM v$version + clipboard.
If it was a legacy edition that did not support REGEXP_SUBSTR() I'd
expect an error message.
Is it possible to cancel, disabled or override REGEXP_SUBSTR?
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
Mark D Powell
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> ddf escribió:
>
> > On Jun 25, 6:29 am, "Álvaro G. Vicario"
> > <alvaro.NOSPAMTH...@demogracia.com> wrote:
> >> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 server,
> >> REGEXP_SUBSTR() always returns NULL. E.g.:
>
> >> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
> >> FROM DUAL
>
> >> In my local 10g XE, however, it returns 1234 as expected.
>
> >> Is it me or is it the server?
> > It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
> > 10.1.0.2 release. You're certain you've posted the version for the
> > Enterprise Edition correctly?
>
> Absolutely: SELECT BANNER FROM v$version + clipboard.
>
> If it was a legacy edition that did not support REGEXP_SUBSTR() I'd
> expect an error message.
>
> Is it possible to cancel, disabled or override REGEXP_SUBSTR?
>
> --
> -- Mi sitio sobre programación web:http://borrame.com
I can duplicate the expected result on 10.2.0.3 running on AIX 5.3L.
Does anyone have a 10.1 version that can be used to duplicate the OP
issue?
I have posted to another forum to see if anyone with a 10.1 system can
duplicate the issue. I suspect a patch level specific issue, but
there could be another cause.
HTH -- Mark D Powell --
ddf
<alvaro.NOSPAMTH...@demogracia.com> wrote:
> ddf escribió:
>
> > On Jun 25, 6:29 am, "Álvaro G. Vicario"
> > <alvaro.NOSPAMTH...@demogracia.com> wrote:
> >> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 server,
> >> REGEXP_SUBSTR() always returns NULL. E.g.:
>
> >> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
> >> FROM DUAL
>
> >> In my local 10g XE, however, it returns 1234 as expected.
>
> >> Is it me or is it the server?
> > It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
> > 10.1.0.2 release. You're certain you've posted the version for the
> > Enterprise Edition correctly?
>
> Absolutely: SELECT BANNER FROM v$version + clipboard.
>
> If it was a legacy edition that did not support REGEXP_SUBSTR() I'd
> expect an error message.
>
> Is it possible to cancel, disabled or override REGEXP_SUBSTR?
>
> --
> -- Mi sitio sobre programación web:http://borrame.com
Things have changed between 10.1 and 10.2:
10;1 regular expression support --
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_posix001.htm#i690819
10.2 regular expression support --
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
These differences may result in the differences you're seeing.
David Fitzjarrell
Maxim Demenko
> On Jun 25, 6:52 am, "�lvaro G. Vicario"
> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>> ddf escribiďż˝:
>>
>>> On Jun 25, 6:29 am, "�lvaro G. Vicario"
>>> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>>>> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 server,
>>>> REGEXP_SUBSTR() always returns NULL. E.g.:
>>>> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
>>>> FROM DUAL
>>>> In my local 10g XE, however, it returns 1234 as expected.
>>>> Is it me or is it the server?
>>> It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
>>> 10.1.0.2 release. You're certain you've posted the version for the
>>> Enterprise Edition correctly?
>> Absolutely: SELECT BANNER FROM v$version + clipboard.
>>
>> If it was a legacy edition that did not support REGEXP_SUBSTR() I'd
>> expect an error message.
>>
>> Is it possible to cancel, disabled or override REGEXP_SUBSTR?
>>
>> --
>> -- Mi sitio sobre programaci�n web:http://borrame.com
>> -- Mi web de humor satinado:http://www.demogracia.com
>> --
>
> Things have changed between 10.1 and 10.2:
>
> 10;1 regular expression support --
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_posix001.htm#i690819
>
> 10.2 regular expression support --
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
>
> These differences may result in the differences you're seeing.
>
>
> David Fitzjarrell
As David already suggested, perl influenced extensions came in 10.2, the
list is at
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix003.htm
On 10.1 one should use instead the posix syntax
REGEXP_SUBSTR('1234,5678', '[[:digit:]]+', 1, 1) or
REGEXP_SUBSTR('1234,5678', '[0-9]+', 1, 1) or
Best regards
Maxim
--
Why make things difficult, when it is possible to make them cryptic
and totally illogical, with just a little bit more effort?
Aksel Peter J�rgensen
"Álvaro G. Vicario"
> ddf schrieb:
>> On Jun 25, 6:52 am, "�lvaro G. Vicario"
>> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>>> ddf escribi�:
>>>
>>>> On Jun 25, 6:29 am, "�lvaro G. Vicario"
>>>> <alvaro.NOSPAMTH...@demogracia.com> wrote:
>>>>> In an Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
>>>>> server,
>>>>> REGEXP_SUBSTR() always returns NULL. E.g.:
>>>>> SELECT REGEXP_SUBSTR('1234,5678', '\d+', 1, 1)
>>>>> FROM DUAL
>>>>> In my local 10g XE, however, it returns 1234 as expected.
>>>>> Is it me or is it the server?
>>>> It may be the server if you are, indeed, comparing XE (10.2.0.1) to a
>>>> 10.1.0.2 release. You're certain you've posted the version for the
>>>> Enterprise Edition correctly?
>>> Absolutely: SELECT BANNER FROM v$version + clipboard.
>>>
>>> If it was a legacy edition that did not support REGEXP_SUBSTR() I'd
>>> expect an error message.
>>>
>>> Is it possible to cancel, disabled or override REGEXP_SUBSTR?
>>>
>>> --
>>> -- Mi sitio sobre programaci�n web:http://borrame.com
>>> -- Mi web de humor satinado:http://www.demogracia.com
>>> --
>>
>> Things have changed between 10.1 and 10.2:
>>
>> 10;1 regular expression support --
>> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_posix001.htm#i690819
>>
>>
>> 10.2 regular expression support --
>> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix001.htm#i690819
>>
>>
>> These differences may result in the differences you're seeing.
>>
>>
>> David Fitzjarrell
>
> As David already suggested, perl influenced extensions came in 10.2, the
> list is at
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_posix003.htm
>
>
> On 10.1 one should use instead the posix syntax
> REGEXP_SUBSTR('1234,5678', '[[:digit:]]+', 1, 1) or
> REGEXP_SUBSTR('1234,5678', '[0-9]+', 1, 1) or
Oh my... That was difficult to spot! I stared at both documents trying
to figure out what had changed, with no luck xD
Of course, that was it: \d was looking for a literal '\d'.
Something I miss in Oracle reference is that it doesn't explain when a
function became available or suffered changes; at least, not in the
function definition. Oh well, thank you everybody. [0-9] is doing the
job just as fine as \d.
--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com