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
INstring2
)
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' )GO
4SELECT PATINDEX( '%fg', 'abcdefg' )GO
6
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.