How to use the STRPOS() function in SQL

The STRPOS() function is used to determine the location in the string where the substring is being matched

Syntax

STRPOS(string,substring)

How does it work?

  • The STRPOS() function searches the substring in the given string and returns the position where the substring is found.

Parameter

  • string: Represents the string to be searched.
  • substring: Represents the string whose position is to be found.

Example

The following code shows how to use the STRPOS() function in SQL.

CREATE TABLE Employee (
    id int,
    first_name varchar(50),
    last_name varchar (50),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Employee
VALUES (01,'Sharon', 'Peller',40000,'Female','Kogi');
INSERT INTO Employee
VALUES (02,'Paul', 'Dons',150000,'Male','Lagos');
INSERT INTO Employee
VALUES (03,'Ameera', 'Abedayo',200000,'Female','Imo');
INSERT INTO Employee
VALUES (04,'Maria', 'Elijah',320000,'Female','Lagos');
INSERT INTO Employee
VALUES (05,'David', 'Hassan',250000,'Male','Abuja');
INSERT INTO Employee
VALUES (06,'Niniola', 'Disu',80000,'Female','Lagos');
INSERT INTO Employee
VALUES (08,'Joe', 'Smith',75000, 'Male','Lagos');
-- Query 
SELECT id, first_name, gender, STRPOS(gender, 'male') AS position
FROM Employee;

Explanation

In the code above:

  • Lines 1–7: We create a table called Employee with the columns id, name, level, gender, and state.
  • Lines 11–24: We input data into the Employee table.
  • Lines 27–28: We return the position where the substring (male) is found in each cell of the gender column using the STRPOS() function. Finally, the result is displayed.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)