July 3, 2013 at 2:11 pm
I know which column is erroring out. It is varchar and I am trying to convert it to decimal. But do we know how to find out which value is
erroing out. It is kind of big table.
Error converting data type varchar to numeric.
July 3, 2013 at 2:26 pm
SELECT * FROM tbl WHERE ltrim(rtrim(col)) NOT LIKE '%[^0-9]%'
Although this will also spit out negative numbers.
On SQL 2012 this is easier where you can use
SELECT * FROM tbl WHERE try_convert(int, col) IS NULL
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 3, 2013 at 2:28 pm
You could try this:
select * from YourTable where isnumeric(YourColumn) = 0
this will at least show rows that are not in proper numeric format. ISNUMERIC can sometimes be fooled but its worth a try.
The probability of survival is inversely proportional to the angle of arrival.
July 3, 2013 at 2:37 pm
you also have to check for empty strings when converting to decimal as well
SELECT * FROM tbl WHERE ltrim(rtrim(col)) NOT LIKE '%[^0-9]%' OR ltrim(rtrim(col))=''
empty string converts to zero for integer, but errors for decimals...
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply