fhg
Volume 8, Number 20 -- May 21, 2008

Use SQL to Strip Out Tab Characters

Published: May 21, 2008

Hey Ted:

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


Sponsored By
HELP/SYSTEMS

Camp Help/Systems

               · Exclusively for System i users
               · Automated Operations
               · Business Intelligence
               · Hands-on pre-camp workshops
               · Learn from the experts
               · Learn from other users
               · Maximize your ROI


Click Here


Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

Aldon:  Keep your organization in line with Application Lifecycle Management solutions
COMMON:  Join us at the Focus 2008 workshop conference, October 5 - 8, in San Francisco, California
Vision Solutions:  System i Management Tips Blog - Free i5/OS Tips Each Week!


 

IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
Getting Started with PHP for i5/OS: List Price, $59.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket Developers' Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
iSeries Express Web Implementer's Guide: List Price, $59.00
Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
WebFacing Application Design and Development Guide: List Price, $55.00
Can the AS/400 Survive IBM?: List Price, $49.00
The All-Everything Machine: List Price, $29.95
Chip Wars: List Price, $29.95


 
The Four Hundred
The Demographics of i Sales and Shipments

The i Edition of the BladeCenter S Finally Launches

HP More Than Doubles Services Biz with EDS Acquisition

Mad Dog 21/21: Saying No No No

A Word Cloud of IBM Server Brand Names

The Linux Beacon
NYSE Euronext Trades Mainframes and Unix for Linux and X64

Canonical Founder Calls for Synchronized Linux Releases

AMD Ships Low-Power Barcelonas as Two More Execs Exit

New and Updated Barcelona Boxes Debut from Sun

VMware Tweaks Virtualization Stack, Boasts of Greenness and Sales

Four Hundred Stuff
Symantec Combats Phishing with New Services Offering

BCD Slings a New C#-Based GUI with Catapult 7.0

SkyView and Innovatum Formalize Partnership with New Product

Profound Eliminates OLTP Requirement with Web Enablement Software

140 Apps and (Hopefully) Counting for i 6.1

Big Iron
NYSE Euronext Trades Mainframes and Unix for Linux and X64

Top Mainframe Stories From Around the Web

Chats, Webinars, Seminars, Shows, and Other Happenings

System i PTF Guide
May 17, 2008: Volume 10, Number 20

May 10, 2008: Volume 10, Number 19

May 3, 2008: Volume 10, Number 18

April 26, 2008: Volume 10, Number 17

April 19, 2008: Volume 10, Number 16

April 12, 2008: Volume 10, Number 15

The Windows Observer
Microsoft Patches Zero Day Flaw in Windows

HP More Than Doubles Services Biz with EDS Acquisition

Massive Expansion in Progress at Microsoft Data Centers

Microsoft Gives Customers a Break on New SMB Windows Packages

AMD Revises Opteron Roadmaps, Pushes Out Rev Gs

The Unix Guardian
New and Updated Barcelona Boxes Debut from Sun

HP More Than Doubles Services Biz with EDS Acquisition

Java Performance Is OS Agnostic on Power6 Gear

As I See It: Soothing the Savage Programmer

VMware Tweaks Virtualization Stack, Boasts of Greenness and Sales

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

THIS ISSUE SPONSORED BY:

WorksRight Software
Help/Systems
Guild Companies


Printer Friendly Version


TABLE OF CONTENTS
Use PCOMM Scripts to Dynamically Build a Spreadsheet, Part 2

Use SQL to Strip Out Tab Characters

Admin Alert: Monitoring the Monitors

Four Hundred Guru

BACK ISSUES

From the IT Jungle Forums
SNMP Traps on i5OS

Java Messages

Copying recs from a subfile to a file and keeping highlights

Imbedded SQL

CPYFRMSTMF problem

Restrict *cmd to specific user





 
Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement