Sep 15, 2015

Case sensitive search on column in SQL Server

Suppose we have a table #TBL with column SEARCH_CONTENT which have values like 'DILIP SINGH', 'dilip singh' and 'Dilip Singh' like below.

CREATE TABLE #TBL
(
      ID INT IDENTITY(1,1),
      SEARCH_CONTENT VARCHAR(500)
)

INSERT INTO #TBL(SEARCH_CONTENT)
SELECT 'DILIP SINGH'
UNION ALL
SELECT 'dilip singh'
UNION ALL
SELECT 'Dilip Singh'

If you will run following script it will return three row of table

SELECT * FROM #TBL WHERE SEARCH_CONTENT LIKE '%DILIP SINGH%'

Result
ID          SEARCH_CONTENT
----------- -----------------------
1           DILIP SINGH
2           dilip singh
3           Dilip Singh

Above query may be change in case sensitive to change collation of query. See following query.

SELECT * FROM #TBL WHERE SEARCH_CONTENT  COLLATE Latin1_General_CS_AS LIKE '%DILIP SINGH%'

Result
ID          SEARCH_CONTENT
----------- ------------------------
1           DILIP SINGH


Here  COLLATE Latin1_General_CS_AS makes the search case sensitive.