8

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%'
14

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.

  • @stom There are two methods. Either a) Move the performance issues to processing time and not select time. You can do this by creating a new column with a subset of the data transformed and then index it, typically during times when you would run ETL. This would have an upkeep cost and is not a great method. B) You can make the query search arguable, or sargable. Changing the query to be SELECT * FROM TABLE WHERE VALUE LIKE %hospitalist or SELECT * FROM TABLE WHERE VALUE LIKE hospitalist% would work. Apart from that, you're looking at hardware or features to increase speed on bad design. – Shaulinator Aug 29 '18 at 13:40
12

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
  • Love it. It's beyond me why SQL couldn't just gracefully fallback like this when you say collate from case sensitive to case insensitive, when you have two otherwise identical collations. I get why you can't go the other way. Anyway this is good stuff. – John Leidegren Oct 29 at 10:23
9

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%' 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.