How to remove blank spaces in records?

We imported approximately 2.9 million records from our mainframe serverinto our SQL Server but have run into a problem. The data in afew of the fields contains both leading and trailing spaces. Anexample of the data would be like this, using periods to representspaces:
What we have:
..1A02938....
What we need:
1A02938 (no spaces)
Is there some sort of algorithm I can run on the data to removethose spaces? The problem is coming up when trying to perform aSELECT query. We try something like:
SELECT * FROM PCPIPT0 WHERE PANO20 = "1A02938" but we get zeroresults because of the spaces in the database. The datatype ofthe filed is char(20) because we need some flexibility on the size ofthe data stored.
Any assistance would be greatly appreciated.
Try this link for packages and code to use to clean the data before you import it into SQL Server. Hope this helps.
http://www.sqldts.com|||

To select and insert the data you can use LTRIM(RTRIM(Pano20)) = "1A02938"

What you might want to do is run an update on the data.
UPDATE PCPIPTo SET PANO20 = LTRIM(RTRIM(PAN020))
This way, you dont have to trim the field each time and get the performance hit.

Nick

|||nick,
Thanks for the response. It took us about 3 hours toimport the data from the AS/400 so if we can just change the data whileit's in the table it would make life a lot easier. I'll try thatupdate query and see how it works.
Thanks!
|||If 1A03928 is indicative of the data stored in that column, I suggestchanging the datatype from char(20) to varchar(20) as well.|||nick,
Thanks for the help. That did the trick! I didn'tneed to do the RTRIM, just the LTRIM. But that makes my life alot easier.