Remove trailing white spaces

[timscronin]

I have some data that was loaded that has trailing white spaces in all of the fields.
Example

lastName location
cronin st. louis

Both fields have several trailing white spaces. Would charindex be the best way to go?

 

[Pattycake245]

rtrim(fieldname) will take off all trailing spaces. If you are trying to save space it might be better to change the data type to varchar.

Tim

 

[SQLBill]

Depends.

What datatype are the fields?

CHAR or NCHAR, forget it you will always have trailing blanks. CHAR(nn) or NCHAR(nn) are a required number of characters and blanks will be used if the value isn't long enough.

VARCHAR or NVARCHAR, check out RTRIM in the BOL. You can UPDATE the rows using RTRIM.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Posting advice: FAQ481-4875

 

[SQLBill]

Sorry, I should have been clearer in my answer. I was talking about how the values are STORED. If you only care about how they are DISPLAYED, then as Pattycake245 says use RTRIM to return the values without the spaces.

-SQLBill

Posting advice: FAQ481-4875

 

[esdee]

LTRIM / RTRIM do not work ?

------------------------

http://esdeeblog.blogspot.com/

dev at viewmoresoft.com

http://www.viewmoresoft.com

tools for your database

 

[esdee]

sorry, didn't notice there were answers by the time i posted my reply

------------------------

http://esdeeblog.blogspot.com/

dev at viewmoresoft.com

http://www.viewmoresoft.com

tools for your database

 

[timscronin]

The values are varchar, I did an update with rtrim and it worked. I was wondering, I tried below and got opposite results. I got the spaces. What would I change to get the name before the spaces

select substring(lname,
charindex(' ',lName)+1 ,
len(lName)) as [Last Name]
from patients

 

[SQLBill]

First of all, let us see why your script failed...

Let's say this is our string:
Smith*** (the *'s are spaces)
Replacing the commands with values, we get this:
SELECT SUBSTRING(LName, 6, 8)
Which 'reads' as return the substring using the LName string, start the substring at the first space and go to the end of the LName string.

What you want to do is start at the beginning of the LName string and go to the first space.

SELECT SUBSTRING(LName, 1, (CHARINDEX('',LName)))

Try that.

-SQLBill

Posting advice: FAQ481-4875

 

[timscronin]

actually when I do the SELECT SUBSTRING(LName, 1, (CHARINDEX('',LName)))
I get all blanks as the answer

 

[Pattycake245]

You must have a leading space or 2 before the name.

Tim