removing special characters string

[timscronin]

I have some data loaded from a mainframe looks like below

lastname firstname id
smith john 12
moris cal 13

The problem is there are what I thought were white spaces at the end of the names. I tried below on test table and with hardcoded white spaces and it worked. Tried it on my data and no work. My guess is there must be some special/hidden character in the data. ANy idea on if I can use what I have below with changes?

update mnfull
set lastname = SUBSTRING(laSTNAME,1,CHARINDEX(' ', lastname)-1)

 

[SQLDenis]

have you tried

replace(laSTNAME, char(13) + char(10),'')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[bborissov]

What type is the field? IF the field is (N)VARCHAR() you wouldn't have any spaces at the end.
What that query gives you:

SELECT LastName, LEN(LastName) AS LenLast
       FROM YourTable

Did LenLast match the length of the LastName field?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[timscronin]

I will refrain for exlaining to my children to pay attention to detail when doing homework. Dad the DBA, did not pay attention the field specs in the table DTS created. Changed it from char to varchar and ran my first statement and it worked. Will now consider applying for Homer Simpson's job when he retires.