Finding Junk Charecters

kamakshi.224 · #1 (**permalink**) 04-22-11, 09:24

Hi,

I am working for Support Project in SSIS.
In that, One of the package is loading data from db2 to SQL server Database tables. But it is failing couple of days because of junk charecter in DB2.

So i have created fix packsge in SSIS to redirect the rows which having junk charecters. And every time i rerunning this when the package failed because of junk charecter.

But i dont want to waste my time to run the fix packge when it failed.

I want to find the junk charecters in the DB2 it self,If the packge fails.

Can any body tell me how to find the junk charecters in db2.

Peter.Vanroose · #2 (**permalink**) 04-22-11, 13:35

Are you sure this is not because of a codepage mismatch?

kamakshi.224 · #3 (**permalink**) 04-25-11, 09:25

Thanks for your reply.

Actually i dont know about the Source. I am getting the junk charecters from Source database DB2. I want to find those junk charecters.

So Can you Please provide me the query that

"How to find junk charecters from DB2".

tonkuma · #4 (**permalink**) 04-25-11, 10:22

What is the definition of junk charecters?

I don't think " junk charecters = 'junk charecters' ".

kamakshi.224 · #5 (**permalink**) 04-26-11, 02:32

Junk charecters means the charecters which we cant read and we cant write mannually.

In my database the text contains the Symbols like Square ,Epsilon..

So I want to find all the symbols...

How?...............

stolze · #6 (**permalink**) 04-26-11, 03:53

What does "read/write manually" mean exactly? After all, I can write an epsilon just fine on paper. With the proper character set, I can also easily set it in a text document and with a greek keyboard, you may have it on the key that usually is for the letter "E" or so.

Basically it comes down to: how do you determine programmatically for each character whether this character is "junk" or not. Please give an algorithm for that.

p.s: One approach could be the isprint() system function on Unix.

tonkuma · #7 (**permalink**) 04-26-11, 08:15

I agree with stolze.
Words like "junk characters", "read/write manually" or "symbols" are not clear for all peoples, especially who are using different character set.

If you can give a string including all junk characters, TRANSLATE function is useful to blank all junk characters or to remove all junk characters.
For example:
TRANSLATE(source_string , '' , junk_characters) -- blank all junk characters
TRANSLATE(source_string , '' , junk_characters , '') -- remove all junk characters

If you can give a string including all valid characters, you may need a little trick.

Here is an example:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 parm(valid_cgaracters) AS (
VALUES ' BCDFGHJKLMNPQRSTVWXYZ'
)
SELECT empno
     , fullname
     , TRANSLATE(  fullname
                 , ''
                 , TRANSLATE(fullname , '' , valid_cgaracters , '')
                )
         AS junk_blanked
     , TRANSLATE(  fullname
                 , ''
                 , TRANSLATE(fullname , '' , valid_cgaracters , '')
                 , ''
                )
         AS junk_removed_1
     , TRANSLATE(fullname , valid_cgaracters , valid_cgaracters || fullname , '')
         AS junk_removed_2
 FROM  (SELECT empno
             , VARCHAR(firstnme || ' ' || lastname , 20) AS fullname
         FROM  employee
       ) e
     , parm
;
------------------------------------------------------------------------------

EMPNO  FULLNAME             JUNK_BLANKED         JUNK_REMOVED_1       JUNK_REMOVED_2      
------ -------------------- -------------------- -------------------- --------------------
000010 CHRISTINE HAAS       CHR ST N  H  S       CHRSTN HS            CHRSTN HS           
000020 MICHAEL THOMPSON     M CH  L TH MPS N     MCHL THMPSN          MCHL THMPSN         
000030 SALLY KWAN           S LLY KW N           SLLY KWN             SLLY KWN            
000050 JOHN GEYER           J HN G Y R           JHN GYR              JHN GYR             
000060 IRVING STERN          RV NG ST RN         RVNG STRN            RVNG STRN           
000070 EVA PULASKI           V  P L SK           V PLSK               V PLSK              
000090 EILEEN HENDERSON       L  N H ND RS N     LN HNDRSN            LN HNDRSN           
000100 THEODORE SPENSER     TH  D R  SP NS R     THDR SPNSR           THDR SPNSR          
000110 VINCENZO LUCCHESSI   V NC NZ  L CCH SS    VNCNZ LCCHSS         VNCNZ LCCHSS        
000120 SEAN O'CONNELL       S  N   C NN LL       SN CNNLL             SN CNNLL            
000130 DELORES QUINTANA     D L R S Q  NT N      DLRS QNTN            DLRS QNTN           
000140 HEATHER NICHOLLS     H  TH R N CH LLS     HTHR NCHLLS          HTHR NCHLLS         
000150 BRUCE ADAMSON        BR C   D MS N        BRC DMSN             BRC DMSN            
000160 ELIZABETH PIANKA      L Z B TH P  NK      LZBTH PNK            LZBTH PNK           
000170 MASATOSHI YOSHIMURA  M S T SH  Y SH M R   MSTSH YSHMR          MSTSH YSHMR         
000180 MARILYN SCOUTTEN     M R LYN SC  TT N     MRLYN SCTTN          MRLYN SCTTN         
000190 JAMES WALKER         J M S W LK R         JMS WLKR             JMS WLKR            
000200 DAVID BROWN          D V D BR WN          DVD BRWN             DVD BRWN            
000210 WILLIAM JONES        W LL  M J N S        WLLM JNS             WLLM JNS            
000220 JENNIFER LUTZ        J NN F R L TZ        JNNFR LTZ            JNNFR LTZ           
000230 JAMES JEFFERSON      J M S J FF RS N      JMS JFFRSN           JMS JFFRSN          
000240 SALVATORE MARINO     S LV T R  M R N      SLVTR MRN            SLVTR MRN           
000250 DANIEL SMITH         D N  L SM TH         DNL SMTH             DNL SMTH            
000260 SYBIL JOHNSON        SYB L J HNS N        SYBL JHNSN           SYBL JHNSN          
000270 MARIA PEREZ          M R   P R Z          MR PRZ               MR PRZ              
000280 ETHEL SCHNEIDER       TH L SCHN  D R      THL SCHNDR           THL SCHNDR          
000290 JOHN PARKER          J HN P RK R          JHN PRKR             JHN PRKR            
000300 PHILIP SMITH         PH L P SM TH         PHLP SMTH            PHLP SMTH           
000310 MAUDE SETRIGHT       M  D  S TR GHT       MD STRGHT            MD STRGHT           
000320 RAMLAL MEHTA         R ML L M HT          RMLL MHT             RMLL MHT            
000330 WING LEE             W NG L               WNG L                WNG L               
000340 JASON GOUNOT         J S N G  N T         JSN GNT              JSN GNT             

  32 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech