Convert sql_variant to varchar to select left 2 I am trying to populate a column (versionstatus) with if the service pack needs updating. I have created a lookup table which lists the SQL Version, and the latest product version, e.g.
SQLVersion           LatestSP
SQL Server 2016      13.0.5026.0 

I then have a second table which contains product versions, and want to compare this value with the LatestSP value, if the value in the first table is less than the second, then SP is not up to date.
I need to specify that where the product version in both tables begins with X (i.e. begins with 12 for SQL 2014, 13 for SQL 2016) then compare the numbers (so that I only compare the correct versions to each other) and if the value in Servers is less than the value in SPLookup, then populate the versionstatus column with 'NEEDS UPDATING'.
 However I am having issues converting sql_variant to varchar to be able to select the first 2 digits. 

Here is what I have so far, and would extrapolate this to the other versions. 
productversion in Servers table is sql_variant, LatestSP in SPLookup is varchar. 

 INSERT INTO Servers 
 (versionstatus)
 SELECT CASE WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion &lt; Y.LatestSP THEN 'UPDATE NEEDED' 
            WHEN LEFT(X.productversion, 2) = '12' AND LEFT(Y.LatestSP, 2) = '12' AND X.productversion &gt;= Y.LatestSP THEN 'UP TO DATE'
        END AS versionstatus
        FROM Servers X INNER JOIN SPLookup Y ON X.productversion = Y.LatestSP
Try explicitly converting the sql_variant column to varchar before applying the LEFT function.  The following simple example worked for me.

Declare @V sql_variant
set @v = '13.0.5026.0'
select left(convert(varchar(max),@v),2)




| (No column name) |
|------------------|
| 13               |