Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Replace characters

    Hi am getting the some special characters in my data

    Sample data:- "3M™BathroomDisinfectantCleaner , i want to replace all specila characters with some default value, finally i have to get numerics and character values only.

    Any one could you help me on this

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please define(or publish) "all specila characters" and "some default value".
    Or define all characters "not specila characters".

    "specila characters" may be different by Language, industry, Organization, so on.


    Needless to say,
    what DB2 version/release and platform OS are you using?

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    Hi special characters may be @#$%^&™ and default value is "

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    i want to replace all specila characters with some default value
    Please use a TRANSLATE function.
    TRANSLATE - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Feb 2012
    Posts
    3
    not able to handle the special character with translate function

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See the example.

    Example 1: Tested on DB2 9.7.5 on Windows
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES CAST( TRANSLATE( CAST('3M™BathroomDisinfectantCleaner' AS VARGRAPHIC(50))
                          , CAST('' AS GRAPHIC(1))
                          , CAST('@#$%^&™' AS GRAPHIC(7))
                          )
                AS VARCHAR(50)
               )
    ;
    ------------------------------------------------------------------------------
    
    1                                                 
    --------------------------------------------------
    3M BathroomDisinfectantCleaner                    
    
      1 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    OR

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES CAST( TRANSLATE( '3M™BathroomDisinfectantCleaner'
                          , '      ' || CAST(' ' AS GRAPHIC(1))
                          , '@#$%^&' || CAST('™' AS GRAPHIC(1))
                          )
                AS VARCHAR(50)
               )
    ;
    ------------------------------------------------------------------------------
    
    1                                                 
    --------------------------------------------------
    3M BathroomDisinfectantCleaner                    
    
      1 record(s) selected.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 02-06-12 at 09:29. Reason: Add comments in sample code.

  9. #9
    Join Date
    Feb 2012
    Posts
    4
    Thanks Tonkuma...I m also searching the same...It works fine..
    Can u explain this translate function alone:

    TRANSLATE(
    '3M™BathroomDisinfectantCleaner@#$%^& Kanji(漢字) 000 ;:#$ ZZZ'
    , ''
    , 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789'
    , ''
    )


    Thanks in advance.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to execute the function by yourself and see the result.
    It will return all non alphanumeric characters(specified by 3rd argument) in the first argument.

    Note: 2nd and 4th arguments are zero length string.
    Then TRANSLATE function removed characters in 3rd argument from first argument.

Posting Permissions

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