The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Hi all,

using SA 11.0.1.2299, I'm having troubles with the regexp_substr() function when used with a starting offset and a pattern including the "start of string" marker (i.e. the caret sign).

Example:

select regexp_substr('a234abcd', '^[[:alpha:]]+', 1)
-> returns 'a'

select regexp_substr('a234abcd', '^[[:alpha:]]+', 5)
-> should return 'abcd' IMHO, but returns null

I would expect the "start of line" marker to apply to the offset, i.e. I would expect that the 2nd example would look for a pattern starting with offset 5.

But that doesn't seem to be the case.

Anyone to confirm this behaviour?


Addition: Would this mean if I want to check for a string that starts with a certain pattern at a particular offset, I would not use the start-offset parameter of regexp_substr() but would instead call this function for the particular substring, i.e.

select regexp_substr(substr('a234abcd', 5), '^[[:alpha:]]+')
-> returns 'abcd'

? That would work as wanted.

asked 20 Jan '10, 13:15

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

edited 20 Jan '10, 14:53


@Volker: You are correct, the output from your second example should be 'abcd', not null. The current behaviour is a bug and will be corrected in a future EBF.

As a work-around, you can use your suggested rewrite. i.e.

regexp_substr( s, p, n )

is the same as

regexp_substr( substr( s, n ), p )

Thank you for reporting the issue.

permanent link

answered 20 Jan '10, 18:11

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

Thanks for the confirmation! I can cope with the workaround - so no need to hurry while bugfixing:)

(20 Jan '10, 18:18) Volker Barth
1

This has been fixed in 11.0.1 build 2379 and up.

(21 Jan '10, 22:17) Mark Culp

I can confirm this behaviour for 11.0.1.2341.

permanent link

answered 20 Jan '10, 15:39

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×3

question asked: 20 Jan '10, 13:15

question was seen: 1,880 times

last updated: 20 Jan '10, 18:11