On 2019-02-12 16:00, Lester Caine [email protected] [firebird-support] wrote: > On 12/02/2019 09:34, Mark Rotteveel [email protected] > [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
