August 14, 2014 at 12:20 pm
DavidL (8/14/2014)
wolfkillj (8/14/2014)
Did you try using the code mickyT posted above to find the rows where the vppblong value contains some character other than the digits 0-9, decimal point (.), and negative sign (-)? Once you find examples of the bad data, you can figure out how best to clean it up. There's almost surely a simpler way than the gyrations you're going through now.
If mickyT's code doesn't return any rows, you probably have non-printing characters in your vppblong column values. Rooting those out can be a little more frustrating, but I can walk you through that if necessary.
I think I see what was happening.
mickyT's code indeed returns many many rows with apparently invalid characters:
SELECT long
FROM #t
WHERE long like '%[^0-9.-]%';
resulted in 434k of about 650k rows.
Some examples are:
- 97.76892600
- 87.90573800
- 88.14191900
- 88.21531600
The above all have a ' ' space. If I REPLACE() that, I think it will work fine. Thanks for all your help. dl
Yup, the REPLACE() is probably that simpler solution you're looking for! 🙂
Since you posted in the SQL Server 2008 forum, I'm assuming you're not working with SQL Server 2012, but when you do upgrade, the TRY_CONVERT() function makes troubleshooting conversion errors with dirty data like this much easier. You can just set up a temp table with the same columns as the input data plus one column of the data type that you want to convert to, then insert the data into it, using TRY_CONVERT() to populate the additional column. The TRY_CONVERT() returns NULL if the input value can't be converted, so all you have to do to see which rows are causing the problem is select from the temp table where the additional column is NULL.
Jason Wolfkill
August 14, 2014 at 12:56 pm
Glad to see it helped.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply