How to do a case-insensitive LIKE in a case-sensitive database?

Question

My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it.

In a case-sensitive database, how would you write this to be case-insensitive?

    Where Name like '%hospitalist%'

Answer 1

You can append a new collation to your select query to find case sensitive or insensitive.

-- Case sensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example
SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just be aware of the performance problems this could present. You will need to scan the clustered index to adjust / find the values when you perform the collation. The way you are writing the LIKE piece also makes the query non-sargable.

I picked up the collation trick from Kendra Little's SELECT Seminar classes. You can find additional collation information though from Ben Snaidero from MS SQL Tips.

MSDN on Collate.

Answer 2

While you can use a scalar function such as UPPER or LOWER and you can re-collate the column so that it's no longer case sensitive, these approaches all require data conversion be done against the base data which will never allow for an index seek. You also are leading your LIKE with a wildcard, so this isn't as much of a concern for you in this scenario anyway, but if you ever wanted to search for the left part of a string in an efficient manner AND allow for the optimizer to seek through an index, you can specify you string with brackets ([]) as follows:

SELECT *
FROM TABLE 
WHERE Name LIKE '[hH][oO][sS][pP][iI][tT][aA][lL][iI][sS][tT]%'

This example (dbfiddle link here) does a better job of showing what I mean:

CREATE TABLE #tmp_cohellation_fun
(
        ID  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,   myValue VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
)

-- Garbage values to represent data you don't want
INSERT INTO #tmp_cohellation_fun
SELECT  CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

-- Sprinkle a little bit of good data
INSERT INTO #tmp_cohellation_fun
        (myValue)
VALUES  ('Apple')
    ,   ('apple')

-- Another healthy helping of garbage that we don't care about
INSERT INTO #tmp_cohellation_fun
SELECT  CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3;

-- Some more good data
INSERT INTO #tmp_cohellation_fun
        (myValue)
VALUES
        ('aPple')
    ,   ('APPLE')
    ,   ('APple')


-- Final insert of garbage that we don't care about
INSERT INTO #tmp_cohellation_fun
SELECT  CAST(NEWID() AS VARCHAR(50))
FROM master.sys.configurations t1
    CROSS JOIN master.sys.configurations t2
    CROSS JOIN master.sys.configurations t3
;

-- Create a nonclustered rowstore index
CREATE INDEX ix_myValue ON #tmp_cohellation_fun (myValue)
;

SET STATISTICS XML ON
;

-- Seek, but incorrect results
SELECT  *
FROM    #tmp_cohellation_fun
WHERE   myValue LIKE 'apple%'
;

-- Scan, with correct results
SELECT  *
FROM    #tmp_cohellation_fun
WHERE   myValue COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'apple%'
;

-- Seek, with correct results
SELECT  *
FROM    #tmp_cohellation_fun
WHERE   myValue LIKE '[aA][pP][pP][lL][eE]%'
;

SET STATISTICS XML OFF
;

DROP TABLE IF EXISTS #tmp_cohellation_fun

Answer 3

Both this and the COLLATE answer will impact performance, due to them making the query non-SARGable, but the easiest way to do so (as Edgar suggested in a comment) is:

WHERE LOWER(Name) LIKE '%hospitalist%' 

or

WHERE UPPER(Name) LIKE '%HOSPITALIST%' 

Answer 4

i would prefer to convert both side of things to either lowercase or uppercase . so that we don't have to bother the case-sensitivity of data present inside the table .

select * from table t where UPPER(t.col1) LIKE UPPER('%TOSEARCH%');

or in java

public List<tableEnt> findCol1FromLike(String search){
List<tableEnt> tableEnt= (List<tableEnt> entityManager
.createQuery("select * from table t where UPPER(t.col1) LIKE UPPER(CONCAT('%',:search,'%'))"))
.setParaneter("search",search)
.getResultlist();
return tableEnt
}