Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
postgresql

How to use the POSITION() function in SQL

Maria Elijah

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;
Using POSITION() to return the position of a substring

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.

RELATED TAGS

sql
postgresql
RELATED COURSES

View all Courses

Keep Exploring