fixing bad characters in SQL?

Question

I've got a database that was migrated from Oracle to SQL Server. One of the tables has data like:

Name
steve
mark
john
matt

when I do this query:

select * from [TABLE] where Name = 'steve'

nothing is returned. If I use SSMS and edit the column by nulling it out and then retyping the data, the query works. So, something is in that character string as a result of the migration that isn't displayed.

Is there any way I can display the more "raw" data of the column so I can try to figure out what this bad data is?

Thanks

UPDATE:

I changed my query a bit and found something interesting:

SELECT LEN(RTRIM(Name)) from [Table]

and what I got was lengths that were longer than 5,4,4,4... so there are certainly some characters after the names but they aren't whitespaces.

Unfortunately I have some values like:

steve
steve1
steve12

so I can't simple go

where NAME like 'steve%' 

as it would incorrectly grab all three rows.

UPDATE (SOLUTION):

Yup, it was some char(13) + 10's.. I used the generate scripts task in SSMS to generate some inserts and it showed some newlines. I just did an UPDATE with REPLACE and all is well. Thanks everybody!

Answer 1

I don't think the problem would be whitespace after the name. I don't think SQL Server would store that anyway. What about leading spaces?

And, yes you can get at the raw data by scripting out the data in the table. In SSMS you can right-click on the database name in the Object Explorer, selects Tasks and then Generate Scripts... This will allow you to generate a schema script and data script. You can then see if you have leading or trailing spaces.