Microsoft SQL Server PatIndex


Example

Returns the starting position of the first occurrence of a the specified pattern in the specified expression.

Parameters:

  1. pattern. A character expression the contains the sequence to be found. Limited to A maximum length of 8000 chars. Wildcards (%, _) can be used in the pattern. If the pattern does not start with a wildcard, it may only match whatever is in the beginning of the expression. If it doesn't end with a wildcard, it may only match whatever is in the end of the expression.

  2. expression. Any string data type.

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3. 

SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3. 

SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start. 

SELECT PATINDEX('inter%', 'interesting') -- Returns 1. 

SELECT PATINDEX('%ing', 'interesting') -- Returns 9.