How To Delete All Non-numerical Letters In Db2

March 21, 2024 Post a Comment

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 (lo

Solution 1:

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

selecttranslate('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.

Solution 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.

SelectTRANSLATE(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:

SelectTRANSLATE(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!@#$%^&*()-=+/\{}[];:.,<>? ')

Solution 3:

As many of the answers above your best approach is to use the TRANSLATE function. However this approach is a different as you can white list the characters you want instead of black list the characters you don't want. We can do this by using the TRANSLATE function twice. We'll use the inner translate to generate a list of characters to remove for the parameter of the outer translate.

selectTRANSLATE(dirty,'',TRANSLATE(dirty,'','1234567890',''),'') as clean
from (Values'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'
      ) as temp(dirty)

Solution 4:

Just taking @Darryls99 and turning it into a UDF

CREATEOR REPLACE FUNCTION REMOVE_ALLBUT(in_string VARCHAR(32000), characters_to_remote VARCHAR(32000))
RETURNSVARCHAR(32000)
LANGUAGESQLCONTAINSSQLDETERMINISTICNOEXTERNAL ACTION
RETURNTRANSLATE(in_string,'',TRANSLATE(in_string,'',characters_to_remote,''),'')
;

use like this

select DB_REMOVE_ALLBUT(s,'1234567890')
from (values'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'
      ) t(s);

which returns

1----7485276430032620696022293798

Solution 5:

Dirty string can be like this: 'qwerty12453lala<<>777*9'

We need to get cleared string and keep only digits.

We could remove any excess characters with TRANSLATE function, but there is one problem: too long and ugly value of 3-th parameter. Something like this:

VALUES
(
TRANSLATE( UPPER('qwerty12453lala<<>777*9'), '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()-=+/\{}[];:.,<>? ')
)

So, this is not very convenient.

My idea is - use TRANSLATE functuion 2 times ( one time inside another one):

Calculate 3-th parameter as a particular list of replaced symbols
Use TRANSLATE function second time to replace excess symbols by using this calculated parameter

Let me show you here in code:

VALUES
( 
  REPLACE --Remove spaces from result
  (  
    TRANSLATE
    (
      UPPER( 'qwerty12453lala<<>777*9')
    , ' '
    , TRANSLATE( UPPER( 'qwerty12453lala<<>777*9') , ' ' , '0123456789')-- This is calculation of 3-th param, it contains only NOT digital characters, like 'QWERTYLALA<<>*'
    )
  , ' '
  , ''  
  )  
)

Result must be like this: 124537779

In case SELECT statement, it would be like this:

SELECT REPLACE
       (
         TRANSLATE( UPPER(T.DIRTY_FIELD), ' ', TRANSLATE(UPPER(T.DIRTY_FIELD), '', '1234567890' ) )
       , ' '
       , ''
       )
  FROM SOMETABLE T

Lab Sql