Perform a Case insensitive Like query in a case sensitive SQL Server database

Question

This is my scenario.

SQL Server 2014 Standard edition, I have a database with a collation SQL_Latin1_General_CP437_BIN2 which is case sensitive.

I want to perform a LIKE query which should return the output irrespective of case sensitive.

Ex: if i execute a Like query to fetch the records with userName 'John' it should also return rows irrespective of case sensitive 'JOHN', 'John', 'john','joHN'.

I tried using Lcase, Ucase, but I am getting the error

Msg 195, Level 15, State 10, Line 4
'LCASE' is not a recognized built-in function name.

This is my sample query

SELECT TOP 300 * 
FROM
    (SELECT 
         userNo, userName, Place, Birthdate
     FROM usertable 
     WHERE personid = 2 
       AND (Name LIKE LCASE('%john%')) 

     UNION 

     SELECT 
         userNo, userName, Place, Birthdate, 
     FROM usertable2 
     WHERE personid = 2 
       AND (Name LIKE LCASE( '%john%') OR Place LIKE LCASE('%NY%')) ) a 
ORDER BY 
    userNo

Guys help me out with your valuable suggestions , I am bit confused of using collation based DB.

SQL Server's functions to change a character value to upper or lower case actually called "UPPER" and "LOWER". ([List of string functions](https://msdn.microsoft.com/en-GB/library/ms181984.aspx).) You may be looking in the documentation for the wrong RDBMS... — Matt Gibson, Oct 19 '15 at 12:07
@Matt Gibson Thanks for pointing me out, i should have used the UPPER / LOWER. — Rajakrishnan, Oct 20 '15 at 05:29

score 19 · Accepted Answer · answered Oct 19 '15 at 12:12

19

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

answered Oct 19 '15 at 12:12

DavidG

113,891
12
217
223

Like to know does your answer has any performance issue by converting a column value to `UPPER` or `LOWER` case then using the `LIKE` to search ? – Shaiju T Aug 29 '18 at 09:28
@stom Of course there will always be a performance hit when calling functions. You can mitigate that with a persisted computed column though. – DavidG Aug 29 '18 at 09:50
@DavidG ,Thanks, could you refer a article, on how to do computed column for case insensitive search ? – Shaiju T Aug 29 '18 at 09:54
Calling UPPER/LOWER should be slower especially for many rows. You need to test this though. It's an easy task. Also there's a difference between UPPER/LOWER and changing COLLATION. Changing collation performs character conversions (accent insensitive, ie. À becomes A) which case conversion DOES NOT! – Paul-Sebastian Manole Oct 07 '19 at 14:47
1

@Paul-SebastianManole Well, it depends on which collation you choose. You can choose case-sensitive which will treat `À` and `A` differently, or accent-sensitive which won't. The `_CI` and `_AI` parts of the collation tell you what it will do. – DavidG Oct 07 '19 at 14:54
@DavidG, I thought that was implied, but true. Also I think you meant `"You can choose accent-insensitive which will treat À and A differently, or accent-sensitive which won't."`, in stead of `"You can choose case-sensitive which will treat À and A differently, or accent-sensitive which won't."`. – Paul-Sebastian Manole Oct 07 '19 at 15:06

score 3 · Answer 2 · edited Oct 19 '15 at 12:16

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.

score 0 · Answer 3 · answered Jan 30 '21 at 00:05

The Syntax Is differ Over Different Db-Provider For Example:

select * from TABLE1 where upper(COL) like 'SOMETHING',, gives syntax error in ODCB Provider for Microsoft... and the right syntax is : select * from TABLE1 where ucase(COL) like 'SOMETHING' ,, i.e. uppercase

So you should use the correct syntax used by your db provider

thanks

score -2 · Answer 4 · answered Oct 19 '15 at 12:32

use code below

SELECT TOP 300 * FROM (SELECT userNo, userName, Place, Birthdate FROM usertable WHERE personid = 2 AND (Name LIKE '%john%') UNION

 SELECT 
     userNo, userName, Place, Birthdate, 
 FROM usertable2 
 WHERE personid = 2 
   AND (Name LIKE '%john%' OR Place LIKE '%NY%') a

ORDER BY userNo

Perform a Case insensitive Like query in a case sensitive SQL Server database

4 Answers4

Linked