Sunday, March 25, 2012

Case sensitive search

I use to apply varbinary clause to go for a case sensitive search.Suppose for the case
use pubs
SELECT * FROM EMPLOYEE WHERE
convert(varbinary,LNAME)=convert(varbinary,'Chang' )

I am getting the correct result in every way.

But when I apply the same rule in the following way
use northwind
SELECT * FROM employees WHERE
convert(varbinary,lastname)=convert(varbinary,'Kin g')

I am not getting any result wheather I pass the correct case sensetive parameter or not.

Can anybody help me why there is a difference in these two results?
One thing I like to mention in the first case the data type is varchar and in the latter case the datatype is nvarchar.Is this not applicable to the fields of type nvarchar.

Subhasishmaybe there's some blanks after LastName :
convert(varbinary,Rtim(LastName))=...|||Indeed nvarchar is the problem

convert(varbinary,lastname)=convert(varbinary,N'Ki ng')

try
SELECT lastname, convert(varbinary,lastname), convert(varbinary,'King')
FROM employees|||this will work for you :

Select * From employees
where
convert(varbinary,convert(varchar,lastname))=
convert(varbinary,'Buchanan')

converting the nvarchar in varchar and then into a varbinary

No comments:

Post a Comment