How to use the POSITION() function in SQL

Overview

The POSITION() function is used to find the position of a substring within a given string.

Syntax

POSITION(search_string in string)

Parameter

  • string: This represents the string to be searched.
  • search_string: This represents the substring whose location is to be found in the string.

Example

CREATE TABLE Writer (
    id int,
    name varchar(50),
    gender varchar(10),
    state varchar(15),
    book_id varchar (20)
);
-- Insert data
INSERT INTO Writer
VALUES (101,'Sharon Peller','Female','Kogi','oo-01-345');
INSERT INTO Writer
VALUES (102,'Paul Dons','Male','Lagos','oo-01-238');
INSERT INTO Writer
VALUES (103,'Ameera Abedayo','Female','Imo','oo-01-103');
INSERT INTO Writer
VALUES (104,'Maria Elijah','Female','Lagos','oo-01-775');
INSERT INTO Writer
VALUES (105,'David Hassan','Male','Abuja','oo-01-788');
INSERT INTO Writer
VALUES (106,'Mara Disu','Female','Lagos','oo-01-924');
INSERT INTO Writer
VALUES (108,'Joe Smith','Male','Lagos','oo-01-245');
-- Query 
SELECT id,name, POSITION('ar' in  name) AS substr_pos
FROM Writer;

Explanation

  • Line 1–7: We create a table called Writer with the columns id, name, gender, state, and book_id.
  • Line 10–23: We input data into the Writer table using the INSERT INTO Writer VALUES() method.
  • Line 26-27: We return the position of the substring ar in the given string name column using the POSITION() function.

Free Resources

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