CODEWITHSUNDEEP
sql-serversql-server-2008data-migration
Matthew Bonig
Matthew Bonig

Reputation: 2136

fixing bad characters in SQL?

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!

Upvotes: 0

Views: 2920

Answers (2)

tgolisch
tgolisch

Reputation: 6734

You could also make a UDF to omit non-alphanum chars. Example: 

Create Function dbo.ReplaceNonAlphaNum(@val varchar(255)) Returns Varchar(255)
BEGIN
   DECLARE @index int

   Set @index = patindex('%[^a-z0-9_]%',@val)
   While @index > 0 
   Begin
      Set @val = stuff(@val,@index,1,'')
      Set @index = patindex('%[^a-z0-9_]%',@val)
   End

   Return @val
END
GO

Grant Exec on dbo.ReplaceNonAlphaNum to Public
GO

-- Test
SELECT dbo.ReplaceNonAlphaNum('_abc@#!123^')
--should give a result of: 'abc123'

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48482

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.

Upvotes: 1

Related Questions