PATINDEX returns the starting position of a pattern within a string.
If the pattern is not found, PATINDEX returns 0.
The first position in the string is 1, not 0.
This example returns the starting position for the '%gogh%' pattern.
SELECT PATINDEX('%gogh%', 'Vincent van Gogh') AS Position
| Position |
|---|
| 13 |
Syntax of the PATINDEX function.
PATINDEX(%pattern%, string)
%pattern% -- a string representing the text sequence.
string -- an expression, variable, or column.
Note: %pattern% accepts % and _ wildcards.
They work just as LIKE.
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT FirstName, LastName
FROM Customer
WHERE PATINDEX('Ja%', FirstName) > 0
| FirstName | LastName |
|---|---|
| Janine | Labrune |
| Janete | Limeira |
| Jaime | Yorres |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT FirstName, LastName
FROM Customer
WHERE PATINDEX('%a_n%', FirstName) > 0
| FirstName | LastName |
|---|---|
| Ann | Devon |
| Hanna | Moos |
| Annette | Roulet |
| Giovanni | Rovelli |
