ltrim and whitespaces

Question

ltrim and whitespaces

Christopher Mark

Old Hand

Points: 367
More actions
May 20, 2006 at 9:18 am

#366213

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Shaun Stewart SSC Eights! Points: 880 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Nope, no trim in 2005 either, just ltrim and rtrim

/Kenneth

Christopher Mark Old Hand Points: 367 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Shaun Stewart SSC Eights! Points: 880 More actions · Answer 7

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.