When using groups within a regex, it is usually possible to "back reference" to such a group via its number (or name), such as "\1" to reference to the first (capturing) group.

Am I right that this is not supported with SQL Anywhere?

Here's a sample:

-- search for a number followed by non-digits and a further number
select regexp_substr('abc-101-abd-102-abe', '(\\d+)\\D+\\d+');
-- -> returns "101-abd-102"

-- If I want the match to restrict to those cases
-- where the first and second number are identical,
-- that requires a backreference via \1, such as
select regexp_substr('abc-101-abd-102-abe', '(\\d+)\\D+\\1'); -- should not match
select regexp_substr('abc-101-abd-101-abe', '(\\d+)\\D+\\1'); -- should match

However, adding '\\1' to the regexp pattern leads to SQLCODE -1135 aka "Invalid regular expression: unsupported syntax in '(\d+)\D+\1'"

I'm using but I guess the regex support has been unchanged for quite a number of versions.

asked 18 Sep '18, 06:56

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

If backreferences are not supported, that would explain the following doc quote IMHO:

(?: pattern ) Non-capturing block
This is functionally equivalent to just pattern, and is provided for compatibility.

If backreferences are not supported, a group needs no capturing, and so a "capturing group" and a "non-capturing group" would be equivalent...

(18 Sep '18, 07:34) Volker Barth

Hi Volker,

SQL Anywhere does NOT support back references in regular expressions.

See https://help.sap.com/viewer/40c01c3500744c85a02db71276495de5/17.0/en-US/81735cab6ce21014b8a287b4fbb8fdc0.html for the list of escaped characters supported, and note that back references are not listed.


permanent link

answered 18 Sep '18, 09:13

Ian%20McHardy's gravatar image

Ian McHardy
accept rate: 40%

Thanks for the confirmation. I noticed that list but was not sure as the docs also state:

For REGEXP and REGEXP_SUBSTR, regular expression syntax and support is consistent with Perl 5.

which includes backreferences AFAIK.

(18 Sep '18, 09:49) Volker Barth
Replies hidden

. . . cannot . . . resist . . . must . . . post . . . this :)

(19 Sep '18, 09:34) Breck Carter

Particularly funny as I came across that one by chance (?) yesterday, too:)

(19 Sep '18, 10:02) Volker Barth
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: 18 Sep '18, 06:56

question was seen: 1,034 times

last updated: 19 Sep '18, 10:03