Use SQL to Strip Out Tab Characters

We have a system set up to import orders from a Website. Sometimes a field comes through from our Website with the tab character. We didn’t realize this is possible and have since started scrubbing the data before it is imported. The problem is that I already have some data in our System i tables that have the tab character in them. This is causing some odd things to happen later in our processes. I need some way to remove all of the tab characters from our database. Do you have a SQL statement or other magic way to find and replace these?

–Daniel

If you want to replace the tab with another character, such as a blank, you can use the TRANSLATE function. Since the tab character is not a printable character, you’ll have to use its hex value, X’05’.

update qtemp/sometable
   set somefield = translate(somefield, ' ', x'05')

Or use REPLACE.

update qtemp/sometable
   set somefield = replace(somefield, x'05', ' ')

Notice I left a blank between the apostrophes.

If you want to squeeze the tab characters out of the string, do this:

update qtemp/sometable
   set somefield = replace(somefield, x'05', '')

Notice there is nothing between those last two apostrophes.

–Ted

Thanks! That worked! I didn’t even think about using hex values.

I thought you might be interested in the tool we are using to import the orders into our System i. We are using an open source tool called Jitterbit, which runs on Windows and Linux. It has the ability to transform data from just about any format to just about any other format. For our Website integration, we are taking flat files from an FTP site and inserting them into tables on the System i using ODBC. For anyone trying to integrate data from another system, I highly recommend Jitterbit. Visit their Website at www.jitterbit.com.

Thanks again. I always enjoy reading your tips in Four Hundred Guru.

–Daniel

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot