Difference between CHARINDEX and PATINDEX

CHARINDEX: It is a string function which returns the starting position of a pattern.

PATINDEX: It is a string function which returns the starting position of a pattern. We can also use wildcard characters in searching for a pattern.

Let’s see some examples to understand the exact difference

First create a table and insert some test data.

-- Test Database
USE udayarumilli
GO
CREATE TABLE MaintLog (Maint_ID INT IDENTITY,

             Activity VARCHAR(100),

             Log_Desc TEXT);

GO

INSERT INTO MaintLog (Activity,Log_Desc)
SELECT 'Maintenace Plan - 112','Executed as admin. DBCC executed for all databases without no errors'
UNION
SELECT 'SSIS Package - S11','Executed as admin. SSIS executed for databases without no errors and imported data into db_199'
UNION
SELECT 'Maintenace Plan - 113','Executed as admin. DBCC executed for database db_tran_113 without no errors'
UNION
SELECT 'SSIS Package - S12','Executed as admin. SSIS executed for databases without no errors and imported data into db_134';

Let’s try these examples with the column “Activity” column

SELECT  Activity,
        CHARINDEX('Maint',Activity)
FROM    MaintLog;

GO

SELECT Activity,
       PATINDEX('%Maint%',Activity)
FROM   MaintLog
WHERE  PATINDEX('%Maint%',Activity) > 0;