STRPOS

9min
This article will walk you through the STRPOS() function with its syntax and a few examples.
OverviewThe 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.
ExamplesCase 1: Basic STRPOS() functionIn 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 columnWe 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 11 May 2023
Did this page help you?
Yes
SUBSTR
REGEXP_REPLACE
Docs powered by archbee
TABLE OF CONTENTS
Overview
Examples
Case 1: Basic STRPOS() function
Case 2: STRPOS() function using column