extract only 0-9 numbers from string

Question

Need to extract all numeric characters i.e. (0-9) from a varchar column. suppose we have data like abc 123 456k, then the output should be '123456', i.e the non numeric characters including spaces to be filtered out. I am on Teradata version 15.10.

I have used REGEXP_SUBSTR(ATRB_7_VAL, '[0-9]+') and REGEXP_REPLACE(ATRB_7_VAL,'[^0-9 ]*',''), but both are not giving desired output.

create multiset volatile table abc
(
ATRB_7_VAL varchar(100)
)
on commit preserve rows;


insert into abc (
'abc 123 456 xyzabck'
);
insert into abc (
'123456 789k'
);
insert into abc (
'123456 789k 678-123*'
);

select ATRB_7_VAL
,REGEXP_SUBSTR(ATRB_7_VAL, '[0-9]+') as val1
,REGEXP_REPLACE(ATRB_7_VAL,'[^0-9 ]*','') as val2

from abc;

expected result

123456
123456789
123456789678123

Actual result with REGEXP_SUBSTR(ATRB_7_VAL, '[0-9]+')

val1
123
123456
123456

actual result with REGEXP_REPLACE(ATRB_7_VAL,'[^0-9 ]*','')

val2
123 456 xyzabck
123456 789k
123456 789k 678-123*

Answer 1

Try replacing the pattern [^0-9]* with empty string:

SELECT REGEXP_REPLACE('abc 123 456k', '[^0-9]*', '')

This should strip off any non digit character, including whitespace.