PATINDEX AND CHARINDEX IN SQL SERVER WITH EXAMPLE

• CHARINDEX and PATINDEX functions return the starting position of a specified pattern.
• The difference between CHARINDEX and PATINDEX are, In CHARINDEX you can specify starting position,in PATINDEX not possible to specify starting position, it will check pattern matching start to END and return the first matched position

DECLARE @TESTINDEX AS TABLE (ID INT ,Remarks VARCHAR(50))

INSERT INTO @TESTINDEX VALUES(1,'Remark:Employee:1 -Raju')

INSERT INTO @TESTINDEX VALUES(2,'Remark:TEmployee:2 -Manu')

INSERT INTO @TESTINDEX VALUES(3,'Remark-TEmployee:2 -Jhon')

INSERT INTO @TESTINDEX VALUES(4,'Remark:DailyWAge:2 -Jhon')

INSERT INTO @TESTINDEX VALUES(5,'Remark-Da:Employee With DailyWage:1-Employee -Jhon')

/*Select Starting Position Using PatIndex*/

SELECT PATINDEX('%EMPLOYEE%',Remarks) AS StartingPosition,* FROM @TESTINDEX

/*Select Starting Position Using CharIndex*/

SELECT CHARINDEX('EMPLOYEE', Remarks) AS StartingPosition,* FROM @TESTINDEX

/*

Filtering Data by specifying Starting Position in CharIndex.

Here 13 is the specified starting Position

check pattern matching after 13th  Position

*/

SELECT CHARINDEX('EMPLOYEE', Remarks,13),* FROM @TESTINDEX

WHERE CHARINDEX('EMPLOYEE', Remarks,13) > 20