PostgreSQL Tutorial: POSITION Function

Summary: The PostgreSQL POSITION() function returns the location of a substring in a string.

Table of Contents

Syntax

The following illustrates the syntax of the PostgreSQL POSITION() function:

POSITION(substring in string)

Arguments

The POSITION() function requires two arguments:

1) substring

The substring argument is the string that you want to locate.

2) string

The string argument is the string for which the substring is searched.

Return Value

The POSITION() function returns an integer that represents the location of the substring within the string.

The POSITION() function returns zero (0) if the substring is not found in the string. It returns null if either substring or string argument is null.

Examples

The following example returns the position of the 'Tutorial' in the string 'PostgreSQL Tutorial':

SELECT POSITION('Tutorial' IN 'PostgreSQL Tutorial');

The result is as follows:

position
----------
       12
(1 row)

Note that the POSITION() function searches for the substring case-sensitively.

See the following example:

SELECT POSITION('tutorial' IN 'PostgreSQL Tutorial');

It returns zero (0), indicating that the string tutorial does not exist in the string 'PostgreSQL Tutorial'.

Remarks

The POSITION() function returns the location of the first instance of the substring in the string.

Consider the following example:

SELECT POSITION('is' IN 'This is a cat');

The result is:

 position
----------
        3
(1 row)

Even though the substring 'is' appears twice in the string 'This is a cat', the POSITION() function just returned the first match.

In this tutorial, you have learned how to use the PostgreSQL POSITION() function to locate a substring in a string.

See more

PostgreSQL Tutorial: String Functions

PostgreSQL Documentation: String Functions and Operators