On 11-2-2019 22:18, Lester Caine les...@lsces.co.uk [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
