[firebird-support] firebird 1.5 installation in linux
Hi, firebird 1.5 linux built is for 32 bits, is it possible to install it in a 64 bit linux? Thanks.
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: [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
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 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 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] Re: Built in RegEx Capability?
On 11-2-2019 23:51, blackfalconsoftw...@outlook.com [firebird-support] wrote: > I believe this has been done with such database engines as SQL Server > and PostgreSQL. > > However, the Firebird Database Engine has a stricter standard as to what > is implemented in the SQL language than other such engines. > Nonetheless, it appears that there is some similar capabilities as the > following link describes... > https://stackoverflow.com/questions/21432507/repetitions-with-regexes-in-firebird That doesn't have to do with a 'stricter standard', it has to do with having a smaller team and having to choose what you do and don't. And in that case, following the ISO-9075 as a guideline instead of inventing your own, is a better solution. But, that said, the SQL standard actually describes more extensive regular expression features than Firebird currently has (eg LIKE_REGEX, OCCURRENCES_REGEX, POSITION_REGEX, SUBSTRING_REGEX, TRANSLATE_REGEX), which btw don't use the atrocity that is the SQL regex used by SIMILAR TO and SUBSTRING(.. SIMILAR TO ...), but the XSLT variant which is similar to Java's and Perl. This also provide support for groups, etc. > It may be more convenient to pull out data via a Regex expression in SQL > code but it is rather redundant since all of the major development > languages already support extensive Regex pattern matching capabilities... It can have its uses where retrieving data out to a programming language and then back again to the database is inefficient. But I agree, in general, a need for this would be symptomatic of having individual data items encoded in a single string. Mark -- Mark Rotteveel
