Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Concatenation
Simple text functions
Modifying strings
15. The POSITION() function
Summary

Instruction

Great! Let's try another function.

There are times when you need to find a certain expression or set of characters in a string. Maybe you don't know what the whole string contains, but you know it has the letters 'br' in it somewhere. To find the index location of this string, use the POSITION() function. It has two parameters: the character string you want to find, the keyword IN, and the string or field to be searched. Here's an example:

SELECT POSITION('br' IN 'abracadabra');

The text 'abracadabra' starts with the letter 'a' (index position 1). The first occurrence of the string 'br' will be at index 2. The position returned by this POSITION() function is indeed 2.

A similar POSITION() function is STRPOS() supported by PostgreSQL, but the arguments are in reverse order and without the keyword IN: first is a string to search in and second is a substring to search:

SELECT STRPOS('abracadabra', 'br');

This query returns 2.

Exercise

In the item table, show the position of the letter 'r' in all item names. Name the column r_index.

Note that POSITION() returns 0 if the pattern is not present in the searched string.

Stuck? Here's a hint!

Use the POSITION() function, like so:

POSITION('r' IN name)