Extracting only Alpha numeric characters from a string

Nithyasankari · #1 (**permalink**) 02-07-12, 00:29

Hi,

How can we extract only alpha numeric characters from a string in DB2??

My input string is mixed with Special characters:
ABCdef@^TM12343"":}}YES
Expected Ouput :
ABCdef12343 YES
(Only to extract Alpha numeric characters excluding the special characters)

Note : Set of Special characters is not known exactly to use translate /replace function.

Is it possible to use NOT function in inside translate/replace?
(Like translate those characters to '' which does not belong to [A-Za-z0-9])

Please help me in this .

Thanks in advance.

tonkuma · #2 (**permalink**) 02-07-12, 00:55

Quote:

Originally Posted by tonkuma

Replace all characters other than 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789' with a blank.

Example 3:

Code:

------------------------------ Commands Entered ------------------------------
VALUES CAST(
          TRANSLATE(
             '3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
           , CAST('' AS GRAPHIC(1))
           , CAST(
                TRANSLATE(
                   '3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
                 , ''
                 , 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
                 , ''
                ) /* TRANSLATE( */
              AS VARGRAPHIC(127)
             ) /* CAST( */
          ) /* TRANSLATE( */
        AS VARCHAR(100)
       ) /* CAST( */
;
------------------------------------------------------------------------------

1                                                                                                   
----------------------------------------------------------------------------------------------------
3M BathroomDisinfectantCleaner       Kanji     000      ZZZ                                         

  1 record(s) selected.

Apply two modifications to Example 3.
1) Replace 2nd parameter of outside TRANSLATE function with CAST('' AS VARGRAPHIC(1))
2) Add 4th parameter for outside TRANSLATE function as CAST('' AS VARGRAPHIC(1))

tonkuma · #3 (**permalink**) 02-07-12, 01:06

Quote:

My input string is mixed with Special characters:
ABCdef@^TM12343"":}}YES
Expected Ouput :
ABCdef12343 YES

Two questions.
1) TM in your input string looks like capital letters and not trademark symbol.
Why were they removed?
2) There is a blank between 2nd 3 and Y in your Expected Ouput.
Why?

Nithyasankari · #4 (**permalink**) 02-07-12, 01:43

Hi,

The symbol is trademark symbol...and there should not be any space in the output...I have typed wrongly...

Thanks...

Internet Services

Web Development

Digital Marketing

Consumer Tech