Conversion failed when casting from nvarchar to int in sql query

Question

SELECT * FROM T1 INNER JOIN T2 ON CONVERT(INT,T1.DeviceID)=T2.LogicalDeviceId

LogicalDeviceId is in int and DeviceID in string

error :Conversion failed when converting the nvarchar value 'NA' to data type int.

Thank you in advance.

Uhm, could it be that this actually needs the sql-server tag instead of mysql? — LukStorms
– LukStorms, Commented Jan 14, 2019 at 14:14
@LukStorms - also possible. The current syntax is TSQL valid. he need to check the tags again. — Sebastian Brosch
– Sebastian Brosch, Commented Jan 14, 2019 at 14:15
Do it by hand. If someone tells you that something's value is NA, how do you convert that into an integer? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 14, 2019 at 14:25

LukStorms · Accepted Answer · 2019-01-14 14:35:47Z

1

Default the 'NA' to NULL.
A NULLIF can be used for that.

This works in MS Sql Server.

SELECT * 
FROM T1 t1
JOIN T2 t2
  ON t2.LogicalDeviceId = CONVERT(INT, NULLIF(t1.DeviceID, 'NA'));

In MS SQL Server 12+ & Azure SQL Database you could use a TRY_CONVERT.
Which instead of an error, will return NULL if the conversion fails.

SELECT * 
FROM T1 t1
JOIN T2 t2
  ON t2.LogicalDeviceId = TRY_CONVERT(INT, t1.DeviceID);

answered Jan 14, 2019 at 13:45

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1