PATINDEX

Returns the starting position of a pattern inside a string.

Syntax

PATINDEX ( string_test_expr , string_expr ) --> INT

Arguments

Parameter	Description
`string_test_expr`	Pattern to find
`string_expr`	String to search within

Test patterns

Pattern	Description
`%`	match zero or more characters
`_`	match exactly one character
`[A-Z]`	match any character between `A` and `Z` inclusive. The `-` character between two other characters forms a range that matches all characters from the first character to the second. For example, [A-Z] matches all ASCII capital letters.
`[^A-Z]`	match any character not between `A` and `Z`
`[abcde]`	match any one of `a` `b` `c` `d` and `e`
`[^abcde]`	match any character that is not one of `a` `b` `c` `d` or `e`
`[abcC-F]`	match `a` `b` `c` or any character between `C` and `F`

Returns

Integer start position of a match, or 0 if no match was found.

Notes

If the value is NULL, the result is NULL.
PATINDEX works on TEXT text types only.
PATINDEX does not work on all literal values - only on column values.

(i.e. PATINDEX('%mimsy%', 'All mimsy were the borogoves') will not work, but PATINDEX('%mimsy%', line) will)

Examples

For these examples, consider the following table and contents:

CREATE TABLE jabberwocky(line TEXT(50));

INSERT INTO jabberwocky VALUES
   ('''Twas brillig, and the slithy toves '), ('      Did gyre and gimble in the wabe: ')
   ,('All mimsy were the borogoves, '), ('      And the mome raths outgrabe. ')
   ,('"Beware the Jabberwock, my son! '), ('      The jaws that bite, the claws that catch! ')
   ,('Beware the Jubjub bird, and shun '), ('      The frumious Bandersnatch!" ');

Simple patterns

t=> SELECT line, PATINDEX('%mimsy%', line) as "Position of mimsy" FROM jabberwocky;
line                                             | Position of mimsy
-------------------------------------------------+------------------
'Twas brillig, and the slithy toves              |                 0
      Did gyre and gimble in the wabe:           |                 0
All mimsy were the borogoves,                    |                 5
      And the mome raths outgrabe.               |                 0
"Beware the Jabberwock, my son!                  |                 0
      The jaws that bite, the claws that catch!  |                 0
Beware the Jubjub bird, and shun                 |                 0
      The frumious Bandersnatch!"                |                 0

Complex wildcards expressions

The following example uses the ^ negation operator to find the position of a character that is neither a number, a letter, or a space.

t=> SELECT PATINDEX('%[^ 0-9A-z]%', line), line FROM jabberwocky;

patindex | line
---------+------------------------------------------------
       1 | 'Twas brillig, and the slithy toves
      38 |       Did gyre and gimble in the wabe:
      29 | All mimsy were the borogoves,
      34 |       And the mome raths outgrabe.
       1 | "Beware the Jabberwock, my son!
      25 |       The jaws that bite, the claws that catch!
      23 | Beware the Jubjub bird, and shun
      32 |       The frumious Bandersnatch!"