PATINDEX

From SQLZoo
Jump to navigation Jump to search
Compatibility
PATINDEX('%s1%', s2)
EngineOKAlternative
ingresNoPOSITION(s1 IN s2)
mysqlNoPOSITION(s1 IN s2)
oracleNoINSTR(s2,s1)
postgresNoPOSITION(s1 IN s2)
sqlserverYes

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

DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects