How to Check if a String Contains a Substring in it in Sql Server.

vishalneeraj-24503
Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 24350
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

Comments or Responses

Login to post response