sql - Like operation returns no rows on nvarchar column filter if the column data start with numeric

Question

I have nvarchar(50) column in SQL Server table and data like this:

123abc
234abc
456abc

My query:

select * 
from table 
where col like '%abc'

Expected result : all rows should be returned Actual result: No rows are returned

Works fine if the column is varchar but returns no rows if the type is nvarchar.

Any ideas?

Answer 1

You probably have spaces at the end of your data. Take a look at this example.

Declare @Temp Table(col nvarchar(50))

Insert Into @Temp(col) Values(N'123abc')
Insert Into @Temp(col) Values(N'456abc ')

Select * From @Temp Where Col Like '%abc'

When you run the code above, you will only get the 123 row because the 456 row has a space on the end of it.

When you run the code shown below, you will get the data you expect.

Declare @Temp Table(col nvarchar(50))

Insert Into @Temp(col) Values(N'123abc')
Insert Into @Temp(col) Values(N'456abc ')

Select * From @Temp Where rtrim(Col) Like '%abc'

According to the documentation regarding LIKE in books on line (emphasis mine): http://msdn.microsoft.com/en-us/library/ms179859.aspx

Pattern Matching by Using LIKE

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all arguments are converted to Unicode and Unicode pattern matching is performed. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. Unicode LIKE is compatible with the ISO standard. ASCII LIKE is compatible with earlier versions of SQL Server.