T-SQL Programming - Useful Tips - Text Search

SQL Server 2008 R2

TEXT SEARCH

Case Sensitive Search
1. CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CI_AS)

    Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

Case Insensitive Search
1. Using CHARINDEX

CHARINDEX('ExpressionToFind', 'ExpressionToSearch' COLLATE Latin1_General_CS_AS)

 - Here CHARINDEX function returns the position of the string found in  the search. Returns 0 if not found.

2. Using Like 
IF EXISTS(SELECT * FROM (SELECT 'This is a test' a) tab WHERE a like '%test')

- 'Like' does case insensitive search in T-SQL