On 3-12-2018 08:46, liviuslivius liviuslivius@...
[firebird-support] wrote:

> Firebird 3
> can someone tell me how to work with substring similar?
> SELECT substring('abc' similar 'a' escape '#')  FROM RDB$DATABASE
> raise an error "Invalid SIMILAR TO pattern"
> SELECT substring('ab11c' similar '[0-9]+' escape '#')  FROM RDB$DATABASE
> raise an error "Invalid SIMILAR TO pattern"
> what i am doing wrong?
> how this pattern should looks like?

The release notes documentation is incomplete (and the described syntax
is wrong: SIMILAR (as working and specified in SQL standard) vs SIMILAR
TO (as documented)).

The problem is that you need to have a capturing group so Firebird knows
which substring to return, this is missing from your example. See
doc/sql.extensions/README.substring_similar.txt

Looking at the SQL standard the error reported by Firebird is wrong (it
should report "data exception — invalid use of escape character." in
this case, absence of a capturing group (or not exactly two instance of
escape and ", in this case #")).

Eg to catch the initial a, you need to use

SELECT substring('abc' similar '#"a#"%' escape '#') FROM RDB$DATABASE

Where # is the escape, and the pair of #" defines the capturing group,
and % means zero or more character. Which means return an 'a' followed
by 0 or more characters.

For the second example, you'd need to use (for example):

SELECT substring('ab11c' similar '[[:ALPHA:]]+#"[0-9]+#"[[:ALPHA:]]+'
escape '#') FROM RDB$DATABASE

which means return 1 or more digits preceded by 1 or more 'alpha' (Latin
letters a..z and A..Z) and followed by 1 or more 'alpha'.

Mark
--
Mark Rotteveel