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 AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

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