SQL in a Nutshell, 3rd Edition

Name

POSITION

The POSITION function returns an integer that indicates the starting position of a string within the search string.

ANSI SQL Standard Syntax

POSITION( string1 IN string2 )

The standard syntax for the POSITION function is to return the first location of string1 within string2. POSITION returns 0 if string1 does not occur within string2 and NULL if either argument is NULL.

MySQL and PostgreSQL

MySQL and PostgreSQL support the ANSI SQL syntax for the POSITION function.

Oracle

Oracle’s equivalent function is called INSTR.

SQL Server

Instead of POSITION, SQL Server supports CHARINDEX and PATINDEX functions. CHARINDEX and PATINDEX are very similar, except that PATINDEX allows the use of wildcard characters in the search criteria.

Examples

/* On MySQL */
SELECT LOCATE('bar', 'foobar');
4

/* On MySQL and PostgreSQL */
SELECT POSITION('fu' IN 'snafhu');
0

/* On Microsoft SQL Server */
SELECT CHARINDEX( 'de', 'abcdefg' )
GO
4
SELECT PATINDEX( '%fg', 'abcdefg' )
GO
6