PATINDEX Function Example in MS SQL Server

PATINDEX function is similar to CHARINDEX function in MSSQL. It checks the existence of a string in another string. If the string is present it returns it's first occurring position. The difference between CHARINDEX and PATINDEX function is that in the later one we can use Wild characters '%' '_' also just like in LIKE operator.

MS SQL Server

Syntax

PATINDEX ( '%pattern%' , expression ) 

Example

select PATINDEX('%is%','This is demo')
---------------------
3

select PATINDEX('% is%','This is demo')
---------------------
5

The following example uses % and _ wildcards to find the position at which the pattern 'Sm', followed by any one character and 'th' starts in the specified string

select PATINDEX('%Sm_th%','This is Smith')
----------
9

select PATINDEX('%Sm_th%','This is Smyth')
----------
9

MySQL

There is no similar function available in MySQL

Back to Converting Functions from MSSQL to MySQL