C # and SQL LIKE: "%" works as a single character pattern

Question

I have a query like this in my DataSet (the database is in the .mdf file):

SELECT *
FROM TableName
WHERE SomeField LIKE @Param

The table TableNamecontains an entry in the field . SomeField

When , it works fine, but when or it returns 0 rows. also works.@Param %@Param % % %%%

Why does '%' work as a one-character pattern?

Answer 1

Your problem is that you should use @param NVARCHAR, not NCHAR

declare @Param nchar(255)
set @Param = N'%'

It's really

N'%             ...' (many more spaces)

Thus, it will not match your data, which

N'             ...' (padded with spaces)