Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

How to use the STRPOS() function in SQL

Maria Elijah

SQL STRPOS() function

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 Student with the columns id, name, level, gender, and state.
  • Lines 11–21: We input data into the Student table.
  • Lines 24–26: We return the number of times the substring male occurred in the gender column using the STRPOS() function. Finally, the result is displayed.

RELATED TAGS

sql
RELATED COURSES

View all Courses

Keep Exploring