[firebird-support] Built in RegEx Capability?
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 p .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? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
Re: [firebird-support] Built in RegEx Capability?
11.02.2019 22:18, Lester Caine les...@lsces.co.uk [firebird-support] wrote: > 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. You could use "Regular expression SUBSTRING" described in firebird3/doc/README.substring_similar.txt. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Built in RegEx Capability?
On 11/02/2019 22:22, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 11.02.2019 22:18, Lester caineles...@lsces.co.uk [firebird-support] wrote: >> 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. > You could use "Regular expression SUBSTRING" described in > firebird3/doc/README.substring_similar.txt. Would help if we did not have to rely on a variable target :( I've got https://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes253.html#rnfb25-dml-regex which the FB3 release notes link back to ... AH ... on a linux installation for SUSE it's hidden in /usr/share/doc/packages/firebird/sql.extensions but it's even less helpful than the online page. None of the examples show pulling the 'content of the second match' to insert in an update statement? They all seem to be returns for Boolean operations? -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Built in RegEx Capability?
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 p .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
Re: [firebird-support] Built in RegEx Capability?
On 12-2-2019 10:34, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > In your case, you will need 3 separate substrings: > > select x, > substring(x similar 'V#"[[:DIGIT:]]+#"%.pdf' escape '#') as VERSION, I just noticed that it is safer to use 'V#"[[:DIGIT:]]+#" %.pdf' (space before %), as I have noticed that quotes make the quoted part greedy, while otherwise it is non-greedy. Eg substring(x similar 'V#"[[:DIGIT:]]+#"%.pdf' escape '#') yields '12' but substring(x similar 'V[[:DIGIT:]]+#"%#".pdf' escape '#') yields '2 p234 The state of the nation' -- Mark Rotteveel
Re: [firebird-support] Built in RegEx Capability?
On 12/02/2019 09:34, Mark Rotteveel m...@lawinegevaar.nl [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 ;) Also is the 'TO' optional? Apparently it's not used, so why is shown in the FB3 release notes? SO now I need to transfer the data over to an FB3 server so I can do it for real rather than on a hacked copy of employee ... -- Lester Caine - G8HFL - Contact - https://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - https://lsces.co.uk EnquirySolve - https://enquirysolve.com/ Model Engineers Digital Workshop - https://medw.co.uk Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
ODP: [firebird-support] Built in RegEx Capability?
Hi.
For your particular case you do not need regex at all. Simple substring +
position.
Example:
Select
D.NAZWA
, SUBSTRING(D.NAZWA FROM POSITION('V', D.NAZWA)+1 FOR POSITION('p', D.NAZWA)-3)
AS V
, SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)+1 FOR POSITION(' ',
SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)))-2) AS P
from
(SELECT 'V12 p234 The state of the nation.pdf' AS NAZWA FROM RDB$DATABASE) D
Pozdrawiam,
Karol Bieniaszewski
Re: [firebird-support] Built in RegEx Capability?
On 2019-02-12 16:00, Lester Caine les...@lsces.co.uk [firebird-support] wrote: > On 12/02/2019 09:34, Mark Rotteveel m...@lawinegevaar.nl > [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
Re: ODP: [firebird-support] Built in RegEx Capability?
On 12/02/2019 16:36, Karol Bieniaszewski liviusliv...@poczta.onet.pl
[firebird-support] wrote:
> Select
> D.NAZWA
> , SUBSTRING(D.NAZWA FROM POSITION('V', D.NAZWA)+1 FOR POSITION('p',
> D.NAZWA)-3) AS V
> , SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)+1 FOR POSITION(' ',
> SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)))-2) AS P
> from
> (SELECT 'V12 p234 The state of the nation.pdf' AS NAZWA FROM
> RDB$DATABASE) D
Moved over to FB3 server now and this works on a few edge cases which
the regex fails on, such as 2 spaces before the 'p', but it also throws
errors where the p element is broken - negative offset.
Nothing is easy :( But I think I can combine to two and populate the new
'V' and 'P' fields.
Part of the problem is being able to list the records properly and if
one was starting from scratch they would probably be '0's padded numbers
but this data has been built up over years and there is no easy way to
rename all the files now ...
--
Lester Caine - G8HFL
-
Contact - https://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - https://lsces.co.uk
EnquirySolve - https://enquirysolve.com/
Model Engineers Digital Workshop - https://medw.co.uk
Rainbow Digital Media - https://rainbowdigitalmedia.co.uk
Re: ODP: [firebird-support] Built in RegEx Capability?
On 2019-02-12 18:50, Lester Caine les...@lsces.co.uk [firebird-support]
wrote:
> On 12/02/2019 16:36, Karol Bieniaszewski liviusliv...@poczta.onet.pl
> [firebird-support] wrote:
>> Select
>> D.NAZWA
>> , SUBSTRING(D.NAZWA FROM POSITION('V', D.NAZWA)+1 FOR POSITION('p',
>> D.NAZWA)-3) AS V
>> , SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)+1 FOR POSITION(' ',
>> SUBSTRING(D.NAZWA FROM POSITION('p', D.NAZWA)))-2) AS P
>> from
>> (SELECT 'V12 p234 The state of the nation.pdf' AS NAZWA FROM
>> RDB$DATABASE) D
>
> Moved over to FB3 server now and this works on a few edge cases which
> the regex fails on, such as 2 spaces before the 'p', but it also throws
> errors where the p element is broken - negative offset.
That sounds like a matter of fine-tuning the expression (which might
require a deeper dive into the syntax).
Mark
