CODEWITHSUNDEEP
sqlsql-serverstored-procedures
NAGARAJA H I
NAGARAJA H I

Reputation: 149

Conversion failed when casting from nvarchar to int in sql query

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.

Upvotes: 1

Views: 88

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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);

Upvotes: 1

Related Questions