CODEWITHSUNDEEP
sql-server
Allan Jardine
Allan Jardine

Reputation: 5463

Conversion failed when using nvarchar, but not varchar

I've run into a situation where I'm getting an error if I run the same query on the same table, the difference being the column type. The query used is:

 SELECT [name] FROM [demo] WHERE [name] = 1111111

If the name column is varchar(7) then it runs no problem.

If the name column is nvarchar(7) then it gives an error:

Conversion failed when converting the nvarchar value 'BBBBBBB' to data type int.

The error makes sense - I can see why the integer value can't be directly compared to the string value. I also realise that I can resolve it with a cast to a string for the condition.

However, what I'm not getting is why the behaviour is different for nvarchar and varchar. This chart appears to suggest that they should have the same behaviour for type conversion.

Does anyone know why this is happening?

Upvotes: 0

Views: 596

Answers (1)

teo van kot
teo van kot

Reputation: 12491

If you using nvarchar column your query should look like:

SELECT [name] FROM [demo] WHERE [name] = N'1111111'

N at the begin of string to make sure that your string is unicode string (nvarchar)

Upvotes: 4

Related Questions