STRPOS
Overview
The STRPOS()
is used 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 orNULL
values. -
If the
substring
is not found in the string, then theSTRPOS()
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('computer', 'ut') AS "Position of ut";
We can see that ut is located at the fifth character of the computer:
+-----------------+
| Position of ut |
+-----------------+
| 5 |
+-----------------+
Case 2: STRPOS() function using column
We have a listofwords table where it stores the word data.
CREATE TABLE listofwords (
words string
);
INSERT INTO listofwords
(words)
VALUES
('corral'),
('traditionally'),
('real'),
('communal'),
('challenge'),
('fall'),
('wall'),
('gallop'),
('albatross');
SELECT * FROM listofwords;
The above query will show the following table:
+----------------+
| 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 words, STRPOS(words, 'al') AS "Position of al"
FROM listofwords;
The result will display 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 |
+----------------+------------------+