The PatIndex function is used to search through a specified expression for a particular pattern and returns to the starting position
of the first occurence of that pattern in the expression.
SQL PatIndex() SYNTAX
Select PATINDEX ( '%pattern%' , expression )
Note: Here 'pattern' is the character string you are searching for and 'expression' is the string in which you are
searching. Normally the expression is a column in a table. The "%" sign is needed on the front and back of the pattern,
unless you are searching for the pattern at the beginning and/or ending of the expression.
h2 class="subheading">PatIndex Function Example
The
Employees table has the following records:
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
| 6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
| 7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
| 9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
| 11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
To find the occurence of the employee's name 'Douglas' from the 'Employees' table, use the 'PatIndex' function on the 'EmployeeName' column
as shown below.
"Select EmployeeID,employeename, PATINDEX ( '%Douglas%' , EmployeeName ) as MatchingName
from
Employees
| 1 |
Richard Hughes |
0 |
| 2 |
Taryn Sinclair |
0 |
| 3 |
Ted Horowitz |
0 |
| 4 |
Jonathan Douglas |
10 |
| 5 |
Miranda Leigh |
0 |
| 6 |
Jana Rae |
0 |
| 7 |
Lita Rosanna |
0 |
| 8 |
Colin Flooks |
0 |
| 9 |
Anthony Frank |
0 |
| 10 |
Stephanie Lynn |
0 |
| 11 |
Jack Martin |
0 |
Note: In the above example, the 'PatIndex' function searches through every value in the 'EmployeeName' column for the matching pattern. If not found,
It returns a value of zero. However when it does find a match, it return the position of the first occurence of the matching pattern.
Hence in the fourth record, it find the matching pattern at the 10th position which it returns.