Hi all,

using SA, 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).


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
accept rate: 34%

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
accept rate: 41%

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

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

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

permanent link

answered 20 Jan '10, 15:39

Martin's gravatar image

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



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:


question asked: 20 Jan '10, 13:15

question was seen: 3,639 times

last updated: 20 Jan '10, 18:11