How to delete all non-numerical letters in db2

Question

I have some data in DATA column (varchar) that looks like this:

Nowshak 7,485 m
Maja e Korabit (Golem Korab) 2,764 m
Tahat 3,003 m
Morro de Moco 2,620 m
Cerro Aconcagua 6,960 m (located in the northwestern corner of the province of Mendoza)
Mount Kosciuszko 2,229 m
Grossglockner 3,798 m

What I want is this:

7485
2764
3003
2620
6960
2229
3798

Is there a way in IBM DB2 version 9.5 to remove/delete all those non-numeric letters by doing something like this:

SELECT replace(DATA, --somekind of regular expression--, '') FROM TABLE_A

or any other ways?

This question follows from this question.

Answer 1

Is there a way in IBM DB2 version 9.5 to remove/delete all those non-numeric letters by doing something like this:

SELECT replace(DATA, --somekind of regular expression--, '') FROM TABLE_A

or any other ways?

No. You will have to create a User Defined Function or implement it in your host application's language.

Answer 2

As suggested in the other question, the TRANSLATE function might help. For example, try this:

select translate('Nowshak 7,485 m','','Nowshakm,') from sysibm.sysdummy1;

Returns:

7 485

Probably with a little tweaking you can get it to how you want it...in the third argument of the function you just need to specify the entire alphabet. Kind of ugly but it will work.