like with nvarchar is not working correctly?

1
votes

Using Microsoft SQL Server 2012 - 11.0.5058.0 (X64) Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

with that creation/fill script :

CREATE TABLE tbl (col CHAR (32) )
insert into tbl values ('test')

Those kind of statements :

declare @var varchar(32) = 'test'
delete from tbl where col like @var

or

delete from tbl where col like 'test'

actually delete the line but why this one :

declare @nvar nvarchar(32) = 'test'
delete from tbl where col like @nvar

do not delete the line ?

1
tsqlsql-server-2012

1 Answers

4
votes

According to this page... https://msdn.microsoft.com/en-us/library/ms179859.aspx

When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant;

Since you are using CHAR data type with length 32, the actual data stored is "test" + 28 spaces.

In your comparison, you are mixing CHAR and nvarchar. Because of the differing data types, SQL Server converts the CHAR data type to NCHAR to perform the comparison.

If you change the data type of the column to VARCHAR, your code works. You could also change your code to:

delete from tbl where col like @nvar + '%'