On 3-12-2018 08:46, liviuslivius liviusliv...@poczta.onet.pl
[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
