Syntax

The following illustrates the syntax of thePOSITION() function:

POSITION(substringin string)

Arguments

ThePOSITION() function requires two arguments:

1)substring

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

2)string

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

Return Value

ThePOSITION() function returns aninteger representing the location of the first instance of the substring within the input string.

ThePOSITION() function returns zero (0) if the substring is not found in the string. It returns NULL if eithersubstring orstring 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 thePOSITION() function searches for the substring case-insensitively.

See the following example:

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

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

The following example uses thePOSITION() function to locate the first string'fateful' in thedescription column of thefilm table from thesample database:

SELECT  POSITION('Fateful' in description ),  descriptionFROM  filmWHERE  POSITION('Fateful' in description )> 0;

Output:

position |                                                   description----------+-----------------------------------------------------------------------------------------------------------------        3 | A Fateful Reflection of a Moose And a Husband who must Overcome a Monkey in Nigeria        3 | A Fateful Yarn of a Lumberjack And a Feminist who must Conquer a Student in A Jet Boat        3 | A Fateful Yarn of a Womanizer And a Feminist who must Succumb a Database Administrator in Ancient India        3 | A Fateful Display of a Womanizer And a Mad Scientist who must Outgun a A Shark in Soviet Georgia...

Remarks

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

For example:

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

Output:

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

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

Summary

• Use thePOSITION() function to locate the first instance of a substring within a string.

Last updated onApril 19, 2024

On this page

• Syntax
• Arguments
• Return Value
• Examples
• Remarks
• Summary