pattern is a sequence of characters to be found from expression. We can specify wildcard characters in pattern. It can be of short character data type.
expression is a string from which pattern to be searched. It is of character string data type.
It returns an integer value.
Examples of PATINDEX Function :
Example 1 : Use of PATINDEX function in select clause
SELECT PATINDEX('%-E%','Syntax-Example')
Output
7
Above example returns position of starting index for characters '-E' in specified string.
SELECT PATINDEX('%l_%','Syntax-Example')
Output
13
Above example returns position of starting index for characters 'l' followed by any character in specified string. Here '%' and '_' are wildcard characters used.
Example 2 : Use of PATINDEX function to display field value of table
SELECT Address, PATINDEX('% %',Address) AS 'Index'
FROM Customers
Output
ContactName Index
Obere Str. 57 6
Avda. de la Constitución 2222 6
Mataderos 2312 10
120 Hanover Sq. 4
Berguvsvägen 8 13
Forsterstr. 57 12
Above example displays starting position of first space in address field from customers table.
Example 3 : Use of PATINDEX function in where clause
SELECT ContactName, Address
FROM Customers
WHERE PATINDEX('%8%',Address) > 0
Output
Christina Berglund Berguvsvägen 8
Diego Roel C/ Moralzarzal, 86
Martine Rancé 184, chaussée de Tournai
Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35
Patricia McKenna 8 Johnstown Road
Jaime Yorres 87 Polk St. Suite 5
Above example displays all customers containing character '8' in addresss from customers table.