TRY_CONVERT does not work?

Question

I am using sql 2016 (SP1), I have a join between two tables:

FROM   table1 t1
JOIN table2 t2
ON t1.varchar(50) = t2.decimal(18,0)

Sometimes it fails with:

Error converting data type varchar to numeric.

In the past, someone corrected the source data and the script stoped failing, but now source data won’t be corrected.

What can I add to prevent it from failing? I do not mind losing that one row that can’t be converted.

I use

WHERE TRY_CONVERT(numeric, varchar(50)) IS NOT NULL

But it still fails.

Re `try_convert does not work`: it does work, but [not necessarily in the order you expect](https://stackoverflow.com/questions/9168597/query-using-a-derived-table-with-isnumeric-results-in-conversion-failure-varcha#comment11533337_9168597). — GSerg, Apr 23 '18 at 10:50
@GSerg worked like a charm, thank you very much!. What I do not understand is: if I use just 'convert' instead of ‘try_convert’, it fails. I also do not understand: if it failed, I thought it had to be one row that was precluding the join, but now I use the ‘try_convert’, the join works, and I get the same amount of rows.. (I was expeting the same amount MINUS the failing one). — Chicago1988, Apr 23 '18 at 11:05
When you get "same" amount of rows with `try_convert`, what are you comparing it to to learn that it is the same? — GSerg, Apr 23 '18 at 11:24
If I remove the join, I get a 100 rows. If I do the join without the try_convert it fails. If I add the join with the try_convert it return a 100 rows. My question is, which of that 100 rows couldn't convert, and what is now able with the try_convert? How could I find the offending row? — Chicago1988, Apr 23 '18 at 12:44
Many other columns failed when doing = t2.decimal(18,0). Could it be that the decimal was before an int? and someone changed the datatype to decimal? — Chicago1988, Apr 23 '18 at 14:35

0 Answers0