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