May 20, 2006 at 9:18 am
i have imported data into sql using a comma delimited file but for some reason when i use the ltrim function nothing happens. Could this be because the whitespaces are being viewed as characters. if so how can i change this.
any help much appreciated
C.M
May 20, 2006 at 11:13 am
You can find out if they are spaces or characters that look like spaces (assuming that all the rows for a given column have leading spaces)...
SELECT ACSII(LEFT(columnname,1))
FROM yourtable
If the come back with a 32, they are spaces and you're doing something wrong with LTRIM.
If they don't come back as 32, write down the number and substitute it in the following to change the abhorant spaces to real spaces...
--===== Change the wierd character to spaces
UPDATE yourtable
SET columnname = REPLACE(columnname,CHAR(thenumber),' ')
It would be helpful if you posted the DDL for the table as well as some copy/paste data if none of the above works.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2006 at 6:15 am
Forgive me if I have misunderstood but ltrim trims leading spaces. If you wish to trim trailing spaces, you should use rtrim or indeed trim which will truncate leading and trailing spaces.
regards
Shaun
Quis custodiet ipsos custodes.
May 22, 2006 at 6:56 am
Yep... that too. For the record, there is no TRIM function in SQL Server 7 or 2000... just LTRIM and RTRIM. Dunno about 2005.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2006 at 8:58 am
Nope, no trim in 2005 either, just ltrim and rtrim
/Kenneth
May 22, 2006 at 5:05 pm
thanks Jeff. the select statement returned 9 which i believe means tab character. Strange because the text file wasnt tab delimited as far as i can see. The replace function ended up doing the trimming for me.
thanks for the help
May 22, 2006 at 5:50 pm
You bet... and to confirm, yes, CHAR(9) is the tab character. You might be able to simplify things if you used the tabl character as a delimiter in the import but I haven't seen the data so dunno for sure...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2006 at 2:16 am
Just to clarify, it is SSIS that supports the TRIM() function
http://msdn2.microsoft.com/en-us/library/ms139947.aspx
It wouldn't have worked in this case though because it only recognises spaces.
My apologies
Shaun
Quis custodiet ipsos custodes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply