Search code examples
firebirdfirebird2.5

Select only digits from Text

Anyone can help how can I select only digits from Text?

Strings can be random like:

ABCD123DEF -> Result must be 123
393SEA981F -> Result must be 393981

Thanks!Firebird version is 2.5

Solution

  • The first that comes to my mind:

    use stored procedure as:

    SET TERM ^ ;

create or alter procedure GET_DIGIT_ONLY (
    IPARAM varchar(32))
returns (
    OPARAM varchar(32))
as
declare variable I integer;
begin
  oparam = '';
  i = 1;
  while (i <= char_length(:iparam)) do
  begin
    if (substring(:iparam from i for 1) similar to '[0123456789]')  then
      oparam = :oparam || (substring(:iparam from i for 1));
    i = :i + 1;
  end
  suspend;
end^

SET TERM ; ^

    How to use:

    execute procedure get_digit_only :input_param
returning_values :output_param

    or

    select get_digit_only.oparam from get_digit_only ('393SEA981F')