STRPOS

7min
Overview
The STRPOS() is used to return the position from where the substring (the second argument) is matched with the string (the first argument).
Syntax
STRPOS(string, substring)
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
Case 1: Basic STRPOS() function
In the example below, we will find the ut (substring) position in the computer (string):
Select strpos
SELECT STRPOS('computer', 'ut') AS "Position of ut";
SELECT STRPOS('computer', 'ut') AS "Position of ut";
﻿
We can see that ut is located at the fifth character of the computer:
Output
+-----------------+
| Position of ut  |
+-----------------+
| 5               |
+-----------------+
+-----------------+
| Position of ut  |
+-----------------+
| 5               |
+-----------------+
﻿
Case 2: STRPOS() function using column
We have a listofwords table where it stores the word data.
Create a Table
CREATE TABLE listofwords (
  words string
);
INSERT INTO listofwords 
    (words) 
VALUES 
    ('corral'),
    ('traditionally'),
    ('real'),
    ('communal'),
    ('challenge'),
    ('fall'),
    ('wall'),
    ('gallop'),
    ('albatross');
CREATE TABLE listofwords (
  words string
);
INSERT INTO listofwords 
    (words) 
VALUES 
    ('corral'),
    ('traditionally'),
    ('real'),
    ('communal'),
    ('challenge'),
    ('fall'),
    ('wall'),
    ('gallop'),
    ('albatross');
﻿
Display the Table
SELECT * FROM listofwords;
SELECT * FROM listofwords;
﻿
The above query will show the following table:
The listofwords Table
+----------------+
| words          |
+----------------+
| corral         |
| traditionally  | 
| real           | 
| communal       | 
| challenge      | 
| fall           | 
| wall           | 
| gallop         | 
| albatross      | 
+----------------+
+----------------+
| words          |
+----------------+
| corral         |
| traditionally  | 
| real           | 
| communal       | 
| challenge      | 
| fall           | 
| wall           | 
| gallop         | 
| albatross      | 
+----------------+
﻿
The following query will display the words and a position of a specific substring = ‘al’ using the STRPOS() function:
Select strpos
SELECT words, STRPOS(words, 'al') AS "Position of al"
FROM listofwords;
SELECT words, STRPOS(words, 'al') AS "Position of al"
FROM listofwords;
﻿
The result will display the al position of different words:
Output
+----------------+------------------+
| words          | Position of al   |
+----------------+------------------+
| corral         | 5                |
| traditionally  | 10               |
| real           | 3                |
| communal       | 7                |
| challenge      | 3                |
| fall           | 2                |
| wall           | 2                |
| gallop         | 2                |
| albatross      | 1                |
+----------------+------------------+
+----------------+------------------+
| words          | Position of al   |
+----------------+------------------+
| corral         | 5                |
| traditionally  | 10               |
| real           | 3                |
| communal       | 7                |
| challenge      | 3                |
| fall           | 2                |
| wall           | 2                |
| gallop         | 2                |
| albatross      | 1                |
+----------------+------------------+
﻿
﻿
Updated 09 Oct 2023
Did this page help you?
SUBSTR
REGEXP_REPLACE
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Examples
Case 1: Basic STRPOS() function
Case 2: STRPOS() function using column