Conversion failed when converting the varchar value to data type int in SQL Server

Question

While getting some data input, for below query, encountered Convertion failed error:-

SELECT a.ProcessInstanceAppianID,
       a.ProcessInstanceDescription,
       CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))AS KPI,
       i.KeyPersonKPILead,
       p.BestPersonName                                     AS 'Lead Person'
FROM   InternalUseOnly..processinstance a
       LEFT JOIN InternalUseOnly..ProjectInput i
              ON i.KeyProjectInput = CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))
       LEFT JOIN snledit..Person p
              ON p.keyperson = i.KeyPersonKPILead
                 AND p.UpdOperation < 2
WHERE  a.KeyProcessStream = 1827
       AND a.ProcessInstanceCompleted IS NULL
       AND a.UpdOperation < 2

It would be very aiding & adding, if anyone can guide me here.

Thanks in anticipation!

Answer 1

The first thing I would do is identify rows where those 6 characters can't possibly be an integer.

SELECT ProcessInstanceAppianID, ProcessInstanceDescription
  FROM InternalUseOnly.dbo.processinstance
  WHERE RIGHT(ProcessInstanceDescription, 6) 
    NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';

Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.

In SQL Server 2012+ you can use TRY_CONVERT() instead.