title: IBM datastage 8.7 script to remove special characters oracle 11g
body: <p>I am wondering if I need to implement a solution at Datastage end and/or Oracle 11g DB end to fix the issue of existence of non-ascii characters in the descriptions. Because the databases are using different character sets, the conversion of one set to another occasionally convert a single non-ascii character into multiple characters causing a truncation error.</p>
  
  <p>Sample description 
  ": ¿What date did this happen?¿ xxxxx: ¿Wednesday, so it would have been ..... "
  ":had to go to the doctor yesterday.¿ xxxxxx: ¿I¿ll just get you to state your"</p>
  
  <p>Ideally (for longer term) I would like to replace with the corresponding character, i.e. some extended quotes should become a regular quote.</p>
  
  <p>For short term i have a sample script written which basically replaces all special characters with a space - </p>
  
  <pre><code>UPDATE rcmain.rc_description
  SET    desc_description  = REPLACE(desc_description, CHR(191), ' ')
  WHERE  desc_description LIKE '%' || CHR(191) || '%'
  </code></pre>
  
  <p>From the above script I was to create a DataStage "ctlCleanseSourceFile" job that calls a UNIX shell script "Replace_extended_characters.sh" to strip the special characters out of the XML files.</p>
  
  <p>This could be done once the XML file are merged into a single file.</p>
  
  <p>Modify one of the attached sample files to create the shell script.</p>
  
  <p>It should:Use the sed statement in the samples, Store a backup of the original file before the replacement. Save this to the same archive directory as the other files for the run &amp; report on the characters replaced if possible.</p>
  
  <p>Is there a better way to handle this situation and not use space to description field.</p>
  
  <p>"Apologies for the long post"</p>