'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 1:[1]

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

Solution 2:[2]

You could make EXECUTE BLOCK

EXECUTE BLOCK
RETURNS (number VARCHAR(20))
AS
  DECLARE VARIABLE temp VARCHAR(20);
  DECLARE VARIABLE temp_char VARCHAR(1);
  DECLARE VARIABLE step INT;
BEGIN
  FOR SELECT
    sometable.sometext
  FROM sometable
  INTO :temp DO
  BEGIN
    step = 1;
    number = '';
    WHILE (:step < CHAR_LENGTH(:temp)) DO
    BEGIN
      temp_char = SUBSTRING(:temp FROM step FOR 1);
      IF (POSITION(:temp_char IN '0123456789') > 0) THEN
        number = :number || :temp_char;

      step = step + 1;
    END
    SUSPEND;
  END
END

OR stored procedure by replacing EXECUTE BLOCK with CREATE OR ALTER PROCEDURE SomeProcedure.

EXECUTE BLOCK is runnable as it is, STORED PROCEDURE require a call like

SELECT SomeProcedure.number
FROM SomeProcedure

Solution 3:[3]

If you have a dirty data source this (Firebird 3.0) function may be of help.

SET TERM ^ ;

CREATE OR ALTER FUNCTION GET_DIGIT_ONLY (IPARAM VARCHAR(15))
RETURNS INTEGER
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE ONUM INTEGER;
DECLARE VARIABLE OPARAM VARCHAR(15);
DECLARE VARIABLE V_WORK VARCHAR(15);
BEGIN
    OPARAM = '';
    ONUM = 0;
    I = 1;
    V_WORK = TRIM(IPARAM);
    IF(V_WORK IS NULL) THEN
    BEGIN
        RETURN 0;
    END
    IF(TRIM(V_WORK) = '') THEN
    BEGIN
        RETURN 0;
    END
    WHILE (I <= CHAR_LENGTH(:V_WORK)) DO
    BEGIN
        IF (SUBSTRING(:V_WORK FROM I FOR 1) SIMILAR TO '[0123456789]')  THEN
            OPARAM = :OPARAM || (SUBSTRING(:V_WORK FROM I FOR 1));
        I = :I + 1;
    END
    IF(TRIM(:OPARAM) = '') THEN
    BEGIN
        RETURN 0;
    END
    ONUM = CAST(:OPARAM AS INTEGER);
    RETURN ONUM;
END;

SET TERM ; ^
;

Following the same idea here is a function to extract alpha characters only:

SET TERM ^ ;

CREATE OR ALTER FUNCTION GET_ALPHA_ONLY (IPARAM VARCHAR(15))
RETURNS VARCHAR(15)
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE OPARAM VARCHAR(15);
DECLARE VARIABLE V_WORK VARCHAR(15);
BEGIN
    OPARAM = '';
    I = 1;
    V_WORK = TRIM(IPARAM);
    IF(V_WORK IS NULL) THEN
        BEGIN
        RETURN '@';
    END
    IF(TRIM(V_WORK) = '') THEN
    BEGIN
        RETURN '@';
    END
    WHILE (I <= CHAR_LENGTH(:V_WORK)) DO
    BEGIN
        IF (SUBSTRING(:V_WORK FROM I FOR 1) SIMILAR TO '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]')  THEN
            OPARAM = :OPARAM || (SUBSTRING(:V_WORK FROM I FOR 1));
        I = :I + 1;
    END
    IF(TRIM(:OPARAM) = '') THEN
    BEGIN
        RETURN '@';
    END
    RETURN OPARAM;
END;

SET TERM ; ^
;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Fernando Murrieta