PATINDEX

Compatibility

PATINDEX('%s1%', s2)
Engine	OK	Alternative
ingres	No	POSITION(s1 IN s2)
mysql	No	POSITION(s1 IN s2)
oracle	No	INSTR(s2,s1)
postgres	No	POSITION(s1 IN s2)
sqlserver	Yes

PATINDEX

PATINDEX('%s1%', s2) returns the character position of the substring s1 within the larger string s2. The first character is in position 1. If s1 does not occur in s2 it returns 0. The match is case insensitive.

   PATINDEX('%ll%' 'Hello world') -> 3      

In this example you return the position of the string 'an' within the name of the country.

SELECT name,
       PATINDEX('%an%', name)
  FROM bbc
 ORDER BY name

SELECT name,
       INSTR(name, 'an')
  FROM bbc

SELECT name,
       POSITION('an' IN name)
  FROM bbc

See also

• SUBSTRING function
• RIGHT function
• LEFT function