SQL in a Nutshell, 2nd Edition

Name

POSITION

Synopsis

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

SQL2003 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.

DB2

DB2’s equivalent function is POSSTR.

MySQL

MySQL supports POSITION as defined in SQL2003.

Oracle

Oracle’s equivalent function is called INSTR.

PostgreSQL

PostgreSQL supports POSITION as defined in SQL2003.

SQL Server

SQL Server has both CHARINDEX and PATINDEX. CHARINDEX and PATINDEX are very similar, except that PATINDEX allows the use of wildcard characters in the search criteria.

Examples

/* DB2 */
SELECT POSSTR('bar', 'foobar');
4
/* 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' )GO4
SELECT PATINDEX( '%fg', 'abcdefg' )GO6