Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Unanswered: Extracting only Alpha numeric characters from a string

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    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))

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  4. #4
    Join Date
    Feb 2012
    Posts
    4
    Hi,

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

    Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •