Skip to main content

POSITION (SQL)

A string function that returns the position of a substring within a string.

Synopsis

POSITION(substring IN string)

Description

POSITION returns the position of the first location of substring within string. The position is returned as an integer. If substring is not found, 0 (zero) is returned. If a NULL value is passed for either argument, POSITION returns NULL.

POSITION is case-sensitive. Use one of the case-conversion functions to locate both uppercase and lowercase instances of a letter or character string.

POSITION, INSTR, CHARINDEX, and $FIND

POSITION, INSTR, CHARINDEX, and $FIND all search a string for a specified substring and return an integer position corresponding to the first match. CHARINDEX, POSITION, and INSTR return the integer position of the first character of the matching substring. $FIND returns the integer position of the first character after the end of the matching substring. CHARINDEX, $FIND, and INSTR support specifying a starting point for substring search. INSTR also supports specifying the substring occurrence from that starting point.

The following example demonstrates these four functions, specifying all optional arguments. Note that the positions of string and substring differ in these functions:

SELECT POSITION('br' IN 'The broken brown briefcase') AS Position,
       CHARINDEX('br','The broken brown briefcase',6) AS Charindex,
       $FIND('The broken brown briefcase','br',6) AS Find,
       INSTR('The broken brown briefcase','br',6,2) AS Inst

For a list of functions that search for a substring, refer to String Manipulation.

Arguments

substring

The substring to search for. It can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).

IN string

The string expression within which to search for substring.

Examples

The following example returns 11, because “b” is the 11th character in the string:

SELECT POSITION('b' IN 'The quick brown fox') AS PosInt

The following example returns the length of the last name (surname) for each name in the Sample.Person table. It locates the comma used to separate the last name from the rest of the name field, then subtracts 1 from that position:

SELECT Name,
POSITION(',' IN Name)-1 AS LNameLen
FROM Sample.Person

The following example returns the position of the first instance of the letter “B” in each name in the Sample.Person table. Because POSITION is case-sensitive, the %SQLUPPER function is used to convert all name values to uppercase before performing the search. Because %SQLUPPER adds a blank space at the beginning of a string, this example subtracts 1 to get the actual letter position. Searches that do not locate the specified string return zero (0); in this example, because of the subtraction of 1, the value displayed for these searches is –1:

SELECT Name,
POSITION('B' IN %SQLUPPER(Name))-1 AS BPos
FROM Sample.Person

See Also

FeedbackOpens in a new tab