Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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]( "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,757 times

last updated: 20 Jan '10, 18:11