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
Go to Top of Page

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))
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -