Cloud

strpos

Use the strpos() function to return the position from where the substring (the second argument) is matched with the string (the first argument).

STRPOS(string, substring)

The input and return must be of type string.

Special cases:

  • Returns NULL if there are no input rows or NULL values.

  • If the SUBSTRING is not found in the string, then the strpos() function will return 0.

Examples

Basic strpos() function

This example shows how to find the ut (substring) position in the computer (string):

SELECT STRPOS('computer', 'ut') AS "Position of ut";

The result shows that ut is located at the fifth character of computer:

+-----------------+
| Position of ut  |
+-----------------+
| 5               |
+-----------------+

strpos() function using column

The listofwords table stores word data:

CREATE TABLE listofwords (
  words text
);
INSERT INTO listofwords
    (words)
VALUES
    ('corral'),
    ('traditionally'),
    ('real'),
    ('communal'),
    ('challenge'),
    ('fall'),
    ('wall'),
    ('gallop'),
    ('albatross');
SELECT * FROM listofwords;

The preceding query shows the table:

+----------------+
| words          |
+----------------+
| corral         |
| traditionally  |
| real           |
| communal       |
| challenge      |
| fall           |
| wall           |
| gallop         |
| albatross      |
+----------------+

The query returns the words and a position of a specific substring = 'al' using the strpos() function:

SELECT words, STRPOS(words, 'al') AS "Position of al"
FROM listofwords;

The result displays the al position of different words:

+----------------+------------------+
| words          | Position of al   |
+----------------+------------------+
| corral         | 5                |
| traditionally  | 10               |
| real           | 3                |
| communal       | 7                |
| challenge      | 3                |
| fall           | 2                |
| wall           | 2                |
| gallop         | 2                |
| albatross      | 1                |
+----------------+------------------+