How does regexp_substr() work with a starting offset and a "start of string" marker?

[VolkerBarth]

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.

[MarkCulp · Accepted solution]

@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.

[VolkerBarth · Accepted solution]

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

[MarkCulp · Accepted solution]

This has been fixed in 11.0.1 build 2379 and up.

[MCMartin]

I can confirm this behaviour for 11.0.1.2341.