(ltrim) and remove the leading spaces

Question

(ltrim) and remove the leading spaces

jchesnut

SSC Rookie

Points: 49
More actions
June 1, 2009 at 2:27 pm

#205383

Hi All,
I am trying to get the two rows on top to aligin (ltrim) and remove the leading spaces but the records but it still comes out as below. I have 348 rows that need to be combine too...
UPDATE (TABLENAME)
SET (COL1) = ltrim(COL1)
------------------------- Incorrect
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
?????????????????????
------------------------- Correct
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
ANY help you can offer would greatly be appreciated.
Tx, JC

Viewing 13 posts - 1 through 12 (of 12 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

jchesnut (6/1/2009)
Hi All,
I am trying to get the two rows on top to aligin (ltrim) and remove the leading spaces but the records but it still comes out as below. I have 348 rows that need to be combine too...
UPDATE (TABLENAME)
SET (COL1) = ltrim(COL1)
------------------------- Incorrect
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
?????????????????????
------------------------- Correct
TUUSALBDMC0001 col2
TUUSALBFIL3001 col2
ANY help you can offer would greatly be appreciated.
Tx, JC

Are trying to eliminate duplicate records from the table??

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Your 2 top rows look finely left trimmed, what are you trying to accomplish, are you trying to remove the spaces between the 2 words?

Can you explain what you are trying to do?

Cheers,

J-F

jchesnut SSC Rookie Points: 49 More actions · Answer 3

The thread doesnt show the leading spaces on the first two rows. I dont want to delete the records I just want the leading spaces out. The records should return 180 distinct servernames and not the 348 that includes servername with leading spaces. Below is the query I run that shows all 348 "distinct" name w/ 168 servernames w/ leading spaces. I hope I answered ur question???

select distinct servername

from fs_filestats;

tx, jc

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

jchesnut (6/1/2009)[hr}
select distinct servername
from fs_filestats;

then you will need to do something like this, to get the distinct trimmed server names.

select distinct LTRIM(servername) as TrimmedServerName

from fs_filestats;

Hope I understood you,

Cheers,

J-F

jchesnut SSC Rookie Points: 49 More actions · Answer 5

I created this code but it still returns centered....aaaarrrrggggh!

I'm not sure if it matters but I used SSIS to import the data into my DB too...:w00t:

select distinct servername,

ltrim(servername)

as ServerName

from fs_filestats

order by servername

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 6

How are you displaying things? If you are running this in SSMS, it should remove the left spaces and return the data correctly. If you doubt that, have you pasted the data into a text file to see if there are spaces?

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

jchesnut (6/1/2009)
I created this code but it still returns centered....aaaarrrrggggh!
I'm not sure if it matters but I used SSIS to import the data into my DB too...:w00t:
select distinct servername,
ltrim(servername)
as ServerName
from fs_filestats
order by servername

What do you mean centered, are you writing to a report, or anything?

By the way, when adding distinct to both of those columns (Servername, and Ltrim(ServerName)), the distinct will not get you the number of rows you asked earlier. It will get all distinct combinations you can have of those 2 columns, which means, all rows.

Cheers,

J-F

jchesnut SSC Rookie Points: 49 More actions · Answer 8

j-f ur right on the code comment but when I just query servername I get all rows back. (522,760)

jchesnut SSC Rookie Points: 49 More actions · Answer 9

sorry....it's not a report just returned in the results window at the bottom.

jchesnut SSC Rookie Points: 49 More actions · Answer 10

steve,

When Importing the data via SSIS there are going to be random blank rows in the .dat files. When that happens it added leading spaces to the next line with data.

tx, jc

jchesnut SSC Rookie Points: 49 More actions · Answer 11

jchesnut

SSC Rookie

Points: 49

June 1, 2009 at 3:36 pm

#1003077

I really appreciate all the help too!!! :w00t:

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 12

Are you using SSMS or something else? I haven't seen anything like this centered. Do the results come back in the grid, or are you using text? I might try text to be sure there are spaces.

Or add another col and include ASCII(substring( col, 1, 1)) to see if there is a space there.