SQL Server: Check a string contains a another string

In SQL Server we can check a string or character is in another string using CHARINDEX function.
CHARINDEX function will return the index of the starting letter of the sub string in the main string. It will return 0 if no match found.
Ex:

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';  
SELECT CHARINDEX('Gopi',@string1)

Result:

We can use it in conditions as below

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Gopi',@string1) > 0   
BEGIN 
   SELECT 'String found'
END
ELSE
BEGIN
   SELECT 'String Not found'
END

 Result:

 If string not found in the main string

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF CHARINDEX('Test',@string1) > 0   
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

 Result:

Note: We can also do it using Like operator as below. But it will not return the index of the string.

DECLARE @string1 VARCHAR(100)='This is Gopi Portal';
IF @string1 LIKE '%gopi%'
BEGIN 
   SELECT 'String found' AS Result
END
ELSE
BEGIN
   SELECT 'String Not found' AS Result
END

Happy Coding 😊!!

Gopikrishna