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 thesubstringin the givenstringand returns the position where thesubstringis 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 dataINSERT INTO EmployeeVALUES (01,'Sharon', 'Peller',40000,'Female','Kogi');INSERT INTO EmployeeVALUES (02,'Paul', 'Dons',150000,'Male','Lagos');INSERT INTO EmployeeVALUES (03,'Ameera', 'Abedayo',200000,'Female','Imo');INSERT INTO EmployeeVALUES (04,'Maria', 'Elijah',320000,'Female','Lagos');INSERT INTO EmployeeVALUES (05,'David', 'Hassan',250000,'Male','Abuja');INSERT INTO EmployeeVALUES (06,'Niniola', 'Disu',80000,'Female','Lagos');INSERT INTO EmployeeVALUES (08,'Joe', 'Smith',75000, 'Male','Lagos');-- QuerySELECT id, first_name, gender, STRPOS(gender, 'male') AS positionFROM Employee;
Explanation
In the code above:
- Lines 1–7: We create a table called
Employeewith the columnsid,name,level,gender, andstate. - Lines 11–24: We input data into the
Employeetable. - Lines 27–28: We return the position where the substring (
male) is found in each cell of thegendercolumn using theSTRPOS()function. Finally, the result is displayed.
TRENDING TOPICS
