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

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 the 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

The above query may be changed in a case sensitive search, see the 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 to search case sensitive.