String length and position functions in PostgreSQL - Mini Project: Build & Apply

Using String Length and Position Functions in PostgreSQL

📖 Scenario: You work in a bookstore database. You want to analyze book titles to find their length and locate specific words inside them.

🎯 Goal: Build SQL queries that use string length and position functions to find the length of book titles and the position of the word 'Guide' in those titles.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert three specific book titles into the books table.

Write a query to select each book's title and its length using the length() function.

Write a query to select each book's title and the position of the word 'Guide' in the title using the position() function.

💡 Why This Matters

🌍 Real World

Bookstore managers often need to analyze book titles for cataloging and searching. Knowing title length and locating keywords helps improve search and display.

💼 Career

Database developers and analysts use string functions to manipulate and query text data efficiently in real-world applications.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Then insert these three rows exactly: (1, 'The Art of War'), (2, 'The Developer Guide'), (3, 'Cooking 101').

PostgreSQL

-- Create the books table and insert the three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write a query to get the length of each book title

Write a SQL query that selects the title and the length of the title using the length() function from the books table.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Art of War'),
(2, 'The Developer Guide'),
(3, 'Cooking 101');

-- Write a SELECT query to get title and length(title)
-- Your code here

Need a hint?

Use SELECT title, length(title) AS title_length FROM books; to get the length of each title.

3

Write a query to find the position of 'Guide' in each title

Write a SQL query that selects the title and the position of the substring 'Guide' in the title using the position() function from the books table.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Art of War'),
(2, 'The Developer Guide'),
(3, 'Cooking 101');

SELECT title, length(title) AS title_length FROM books;

-- Write a SELECT query to get title and position of 'Guide' in title
-- Your code here

Need a hint?

Use position('Guide' IN title) to find where 'Guide' starts in each title.

4

Combine length and position queries into one

Write a SQL query that selects the title, the length of the title as title_length, and the position of the substring 'Guide' in the title as guide_position from the books table.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
(1, 'The Art of War'),
(2, 'The Developer Guide'),
(3, 'Cooking 101');

SELECT title, length(title) AS title_length FROM books;

SELECT title, position('Guide' IN title) AS guide_position FROM books;

-- Write a single SELECT query to get title, length(title), and position of 'Guide'
-- Your code here

Need a hint?

Combine both functions in one SELECT statement.