Tuesday, September 9, 2008

Use SQL to remove extra spaces:

The REPLACE function can be used to remove extra spaces within a character string. IBM added the REPLACE function to SQL in V5R3.

Run the following query to see what would happen.
select name,
replace(replace(replace(name,' ','<>'),'><',''),'<>',' ')
from qtemp/mydata

This is the output:

NAME REPLACE
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith
Joe Smith Joe Smith

So how does it work? The innermost REPLACE changes all blanks to a less-than greater-than pair. So, if there are three spaces between Joe and Smith, the innermost REPLACE returns Joe<><><>Smith.

The middle REPLACE changes all greater-than less-than pairs to the empty string, which removes them. Joe<><><>Smith becomes Joe<>Smith.

The outer REPLACE changes all less-than greater-than pairs to a single blank. Joe<>Smith becomes Joe Smith.

You do not have to use the less-than and greater-than symbols. Any two characters that are not used in the field will work.

No comments: