With the help of CharIndex in-built Sql-Server method, we can check whther string contains substring in it.
For example:-
DECLARE @str VARCHAR(50);
SET @str = 'Vishal Kumar Neeraj'
IF CHARINDEX('Kumar',@str ) > 0
PRINT 'Yes it there in string';
ELSE
PRINT 'It doesn''t present';
Output will be:- Yes it there in string. Let's take same example:-
DECLARE @str VARCHAR(50);
SET @str = 'Vishal Kumar Neeraj'
IF CHARINDEX('KumarNeeraj',@str ) > 0
PRINT 'Yes it there in string';
ELSE
PRINT 'It doesn''t present';
Output will be:- It doesn't present. Meaning that,CharIndex() Searches only particular Text inside String WITHOUT SPACE IN-BETWEEN TEXT.
CharIndex function returns Text Position Number,if it finds exact search then returns Position Index otherwise returns 0.
For Example:-
DECLARE @str VARCHAR(50);
Declare @index_position Int;
SET @str = 'Vishal Kumar Neeraj'
Select @index_position = CHARINDEX('Kumar Neeraj',@str);
Select @index_position
It will return : 8 Modify the code:-
DECLARE @str VARCHAR(50);
Declare @index_position Int;
SET @str = 'Vishal Kumar Neeraj'
Select @index_position = CHARINDEX('KumarNeeraj',@str);
Select @index_position
It will return : 0