Use SQL to Remove Extra Spaces
October 11, 2006 Hey, Ted
|
The SQL sorting tip you published on September 13 was a good one. I had seen a similar tip written by Craig Mullins in Quest Software’s Pipeline Newsletter. Mullins had two other interesting tips on the same page. I thought your readers might like to see them as well. –Tom The Web page to which Tom refers is at http://www.quest-pipelines.com/newsletter-v7/0606_F.htm. Craig Mullins uses the technique about which I wrote to sort data on three-letter day abbreviations. Another tip he presents involves using the REPLACE function to remove extra spaces within a character string. IBM added the REPLACE function to SQL in V5R3. I ran the following query to see what would happen, and it worked like a charm.
select name,
replace(replace(replace(name,' ','<>'),'>',' ')
from qtemp/mydata
Here’s what I saw: 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. Clever! 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. As for the other technique, I did not quite catch Craig Mullins’ drift, but maybe it was the example he gave. –Ted RELATED STORY: |
