'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.
- Thanks to Val Marinov for the original function.
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 |