Perform a Case insensitive Like query in a case sensitive SQL Server database
In addition to using lower()
, you need to apply it to the column not the pattern. The pattern is already lower case.
Select top 300 a.*
from (SELECT userNo, userName, Place, Birthdate
FROM usertable
where personid = 2 and lower(Name) LIKE '%john%'
UNION
SELECT userNo, userName, Place, Birthdate
FROM usertable2
where personid = 2 and
(lower(Name) like '%john%' or lower(Place) like '%ny%')
) a
order by userNo;
Note that UNION ALL
is preferable to UNION
, unless you intentionally want to incur the overhead of removing duplicates.
You can use UPPER
or LOWER
functions to convert the values to the same case. For example:
SELECT *
FROM YourTable
WHERE UPPER(YourColumn) = UPPER('VALUE')
Alternatively, you can specify the collation manually when comparing:
SELECT *
FROM YourTable
WHERE YourColumn = 'VALUE' COLLATE SQL_Latin1_General_CP1_CI_AI