On 2019-02-12 16:00, Lester Caine lester@... [firebird-support]
wrote:

> On 12/02/2019 09:34, Mark Rotteveel mark@...
> [firebird-support] wrote:
>> You may want to look at the discussion "substring similar - "Invalid
>> SIMILAR TO pattern"" on the 5th of December 2018.
>>
>> In your case, you will need 3 separate substrings:
>>
>> select x,
>> substring(x similar 'V#"[[:DIGIT:]]+#"%.pdf' escape '#') as
>> VERSION,
>> substring(x similar 'V[[:DIGIT:]]+ p#"[[:DIGIT:]]+#" %.pdf' escape
>> '#') as PAGE,
>> substring(x similar 'V[[:DIGIT:]]+ p[[:DIGIT:]]+ #"%#".pdf' escape
>> '#') as TITLE
>> from (
>> select 'V12 p234 The state of the nation.pdf' as x
>> from rdb$database
>> ) a
>>
>> You need to provide a full match, and the part you want to extract
>> should be enclosed in double quotes, but those double quotes most be
>> escaped (I used # here).
>
> Thanks Mark ... just the kick I needed ...
>
> OK looks like the first thing I was missing is that this only works in
> FB3? Despite referencing FB2.5 release notes to expand the expressions?
> Typically the data I'm working with is still on the FB2.5 server ;)

Substring(... similar ...) was added in Firebird 3, but it uses the same
regular expression syntax as similar to (which was introduced in
Firebird 2.5).

> Also is the 'TO' optional? Apparently it's not used, so why is shown in
> the FB3 release notes?

That is an error in the release notes. It is substring(... similar ...),
not substring(... similar to ...); probably a result of the similarity
with similar to.

Mark