On 11-2-2019 22:18, Lester Caine lester@... [firebird-support]
wrote:

> I have some data in a table which I need to 'pull apart'. It's
> essentially a ID for a reference but it's not as tidy as I would like
> because of the variable length and a little variable in case although
> the format is consistent.
>
> V<num> p<num> <Name>.pdf
> For example 'V12 p234 The state of the nation.pdf'
>
> Ideally I need to extract the 'V' element and the 'p' element into their
> own fields so I can look up Volume '12' and page '234' or list all the
> pdf's for Volume '12' in page order.
>
> I can run it through PHP and RegEx the text and push the new fields
> back, but is it possible to do this in a query in Firebird. I THINK what
> I'm seeing is that I could search for matching patterns but not actually
> extract the match to a new field?
>

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).

Mark
--
Mark Rotteveel