Extract number from a string field (Fb 2.5)

[Mr John]

Hi

what is the best solution to extract number from a string field,in firebird 2.5?

for example

  ABC134       ---> 134

  R567XYX     ---> 567

thanks

[Dimitry Sibiryakov]

Mr John wrote 25.11.2021 11:37:
> what is the best solution to extract number from a string field,in firebird 2.5?

The only way is an UDf or an ugly loop with SUBSTRING() in PSQL.

--
WBR, SD.

[Mark Rotteveel]

In Firebird 2.5, you'll need a UDF or PSQL code to do this. In Firebird
3.0 and higher you can use the regular expression variant of
SUBSTRING[1], for example substring(val similar '%#"[[:DIGIT:]]+#"%'
escape '#')[2]

[1]:
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-functions-string.html#fblangref30-scalarfuncs-substring
[2]:
https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=ca94c333280dfbe2c2bd94aceee293c5

[Mr John]

thanks both of you

I've created a stored procedure:

create or alter procedure  FnToBigin(num varchar(20))

returns (result bigint)

as

declare i integer;

declare res varchar(20);

begin

res='';

result=null;

  i=1;

   while (i<=char_length(:num)) do

    begin

     if (substring(:num from i for 1) similar to '[[:DIGIT:]]') then res =res||substring(:num from i for 1);

     i=i+1;

    end

  if (res<>'') then result=cast(res as bigint);

suspend;

end

and I use it:

select max(res) from

(SELECT (SELECT RESULT FROM FnToBigin(NRU)) as res FROM MYTABLE)

I don't know about the performance over a big table,In this case I need also to run Max()

thanks

--
You received this message because you are subscribed to the Google Groups "firebird-general" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-gener...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/f0fb6a8700d2a40970da555aba79dc60%40lawinegevaar.nl.

[Mark Rotteveel]

On 2021-11-25 12:58, Mr John wrote:
> thanks both of you
> I've created a stored procedure:
>
> create or alter procedure FnToBigin(num varchar(20))
> returns (result bigint)
> as
> declare i integer;
> declare res varchar(20);
>
> begin
>
> res='';
> result=null;
> i=1;
> while (i<=char_length(:num)) do
> begin
> if (substring(:num from i for 1) similar to '[[:DIGIT:]]') then
> res =res||substring(:num from i for 1);
> i=i+1;
> end
> if (res<>'') then result=cast(res as bigint);
> suspend;
> end

I would suggest using an extra local CHAR(1) variable to store the
result of the substring so you perform that only once instead of twice.

> and I use it:
>
> select max(res) from
> (SELECT (SELECT RESULT FROM FnToBigin(NRU)) as res FROM MYTABLE)
>
> I don't know about the performance over a big table,In this case I
> need also to run Max()

You may want to consider if it is worthwhile to add an extra column to
the table and populate it using an ON INSERT OR UPDATE trigger instead
of doing this on the fly. That really depends on how often you need to
do this though.

Mark

[Mr John]

indeed,good advice.thanks

--
You received this message because you are subscribed to the Google Groups "firebird-general" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-gener...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-general/bac56fc40498613f2040bc9dfbada763%40lawinegevaar.nl.