- 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