SQL PATINDEX Function

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.

Example

#

This example returns the starting position for the '%gogh%' pattern.

SELECT PATINDEX('%gogh%', 'Vincent van Gogh') AS Position
Result:  1 record
Position
13

Syntax

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.


More Examples

PATINDEX with % wildcard

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List customers whose first name starts with Ja.
SELECT FirstName, LastName
  FROM Customer
 WHERE PATINDEX('Ja%', FirstName) > 0
Result:  3 records
FirstName LastName
Janine Labrune
Janete Limeira
Jaime Yorres

PATINDEX with _ WILDCARD

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List customers that have an 'a', followed by any character, and then an 'n' in their first name.
SELECT FirstName, LastName
  FROM Customer
 WHERE PATINDEX('%a_n%', FirstName) > 0
Result:  4 records
FirstName LastName
Ann Devon
Hanna Moos
Annette Roulet
Giovanni Rovelli

You may also like



Guides


vsn 3.1