Trim Leading and Trailing Spaces Not Working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trim Leading and Trailing Spaces Not Working

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-30 : 09:03:35

This is not working and I have used this plenty of times:


UPDATE Reps SET LName = RTRIM(LTRIM(LName))

The column in question is a VARCHAR(50)
When this simple script is run, it does not remove the spaces after the name.

I do a select * on the table, copy the LName in the results using SQL Server Management, paste it onto the query window to see it and the trailing spaces remain.

This is not a tab. I tried a REPLACE(LName, CHAR(9), '')

What else can be included in the field except spaces?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-30 : 09:13:50

The usual suspects: carriage returns, line feeds, tab characters

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-30 : 09:15:37

Try converting to binary to see what the leading and trailing characters are:

SELECT CAST(Lname AS VARBINARY(400))

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-30 : 09:20:03

I converted one of them to binary and this is the result:

0x426F626273202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020

Also tried to discover the ASCII code of the spaces using this and it did not print out the spaces in the results:

DECLARE @Position INT = 0
DECLARE @String VARCHAR(50) = 'Bobbs                                             '

WHILE @Position < LEN(@String)
BEGIN
SELECT ASCII(SUBSTRING(@String, @Position+1, @Position+2)) AS ASCIICODE,
CHAR(ASCII(SUBSTRING(@STring, @Position+1, @Position+2))) AS CHARVALUE

SET @Position = @Position + 1
END

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-03-30 : 09:51:53

I know what the problem is now.
On my dev and staging servers, I already adjusted the columns from CHAR(50) to VARCHAR(50).
I haven't done it yet on production and there's the problem.

Thanks everyone for your input!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources