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

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.

Answer 2

One easy way to accomplish that is to use the TRANSLATE(value, replacewith, replacelist) function. It replaces all of the characters in a list (third parameter) with the value in the second parameter.

You can leverage that to essentially erase all of the non-numeric charaters out of the character string, including the spaces.

Just make the list in the third parameter contain all of the possible characters you might see that you don't want. Translate those to an empty space, and you end up with just the characters you want, essentially erasing the undesired characters.

Note: I included all of the common symbols (non-alpha numeric) for the benefit of others who may have character values of a larger variety than your example.

Select 
   TRANSLATE(UCASE(CHAR_COLUMN),'',
            'ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()-=+/\{}[];:.,<>? ')
FROM TABLE_A

More simply: For your particular set of values, since there is a much smaller set of possible characters you could trim the replace list down to this:

Select 
   TRANSLATE(UCASE(CHAR_COLUMN),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ(), ')
FROM TABLE_A

NOTE: The "UCASE" on the CHAR_COLUMN is not necessary, but it was a nice enhancement to simplify this expression by eliminating the need to include all of the lower case alpha characters.

TRANSLATE(CHAR_COLUMN,'',
      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!@#$%^&*()-=+/\{}[];:.,<>? ')