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

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

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

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

Hint

Combine both functions in one SELECT statement.

Practice

1. What does the LENGTH function do in PostgreSQL?

easy

A. Splits a string into an array

B. Finds the position of a substring inside a string

C. Converts a string to uppercase

D. Returns the number of characters in a string

Solution

1. Step 1: Understand LENGTH function purpose

   The LENGTH function counts how many characters are in a given string.

2. Step 2: Compare with other options

   POSITION finds substring location, UPPER changes case, SPLIT divides string. Only LENGTH counts characters.

3. Final Answer:

   Returns the number of characters in a string -> Option D

4. Quick Check:

   LENGTH = character count [OK]

Hint: LENGTH counts characters, POSITION finds substring start [OK]

Common Mistakes:

• Confusing LENGTH with POSITION
• Thinking LENGTH changes string case
• Assuming LENGTH splits strings

2. Which of the following is the correct syntax to find the position of substring 'cat' in string 'concatenate' using PostgreSQL?

easy

A. LENGTH('cat' IN 'concatenate')

B. POSITION('concatenate' IN 'cat')

C. POSITION('cat' IN 'concatenate')

D. FIND('cat', 'concatenate')

Solution

1. Step 1: Recall POSITION syntax

   POSITION(substring IN string) finds where substring starts in string.

2. Step 2: Check each option

   POSITION('cat' IN 'concatenate') matches syntax. POSITION('concatenate' IN 'cat') reverses arguments. LENGTH('cat' IN 'concatenate') uses LENGTH wrongly. FIND('cat', 'concatenate') uses non-existent FIND function.

3. Final Answer:

   POSITION('cat' IN 'concatenate') -> Option C

4. Quick Check:

   POSITION(substring IN string) correct syntax [OK]

Hint: Use POSITION('sub' IN 'string') to find substring start [OK]

Common Mistakes:

• Swapping substring and string arguments
• Using LENGTH instead of POSITION
• Using non-PostgreSQL functions like FIND

3. What is the result of this query?

SELECT POSITION('day' IN 'holiday') AS pos, LENGTH('holiday') AS len;

medium

A. pos = 4, len = 7

B. pos = 5, len = 7

C. pos = 0, len = 7

D. pos = 4, len = 6

Solution

1. Step 1: Find POSITION of 'day' in 'holiday'

   'holiday' positions: 1:'h', 2:'o', 3:'l', 4:'i', 5:'d', 6:'a', 7:'y'. 'day' matches positions 5('d')-6('a')-7('y'), so starts at 5 (1-based).

2. Step 2: Calculate LENGTH of 'holiday'

   'holiday' has 7 characters.

3. Final Answer:

   pos = 5, len = 7 -> Option B

4. Quick Check:

   POSITION('day' IN 'holiday') = 5, LENGTH('holiday') = 7 [OK]

Hint: Count characters carefully; POSITION is 1-based [OK]

Common Mistakes:

• Counting POSITION starting at 0
• Miscounting substring start
• Confusing LENGTH with POSITION

4. Identify the error in this query:

SELECT POSITION('test', 'contest') AS pos;

medium

A. Uses comma instead of IN keyword in POSITION function

B. Missing AS keyword for alias

C. POSITION does not accept string literals

D. Syntax is correct, no error

Solution

1. Step 1: Check POSITION function syntax

   POSITION requires syntax POSITION(substring IN string), not POSITION(substring, string).

2. Step 2: Identify error in query

   The query uses comma instead of IN keyword, causing syntax error.

3. Final Answer:

   Uses comma instead of IN keyword in POSITION function -> Option A

4. Quick Check:

   POSITION(substring IN string) correct syntax [OK]

Hint: Use IN keyword, not comma, in POSITION function [OK]

Common Mistakes:

• Using comma instead of IN in POSITION
• Assuming alias AS is mandatory (it's optional)
• Thinking POSITION can't use string literals

5. You want to find the position of the first space character in the string 'Find the position'. Which query will correctly return this position?

hard

A. SELECT POSITION(' ' IN 'Find the position');

B. SELECT LENGTH(' ' IN 'Find the position');

C. SELECT POSITION('space' IN 'Find the position');

D. SELECT POSITION(' ' FROM 'Find the position');

Solution

1. Step 1: Identify correct function usage for substring position

   POSITION(substring IN string) finds where substring starts. We want the space character ' '.

2. Step 2: Evaluate each option

   SELECT POSITION(' ' IN 'Find the position'); uses correct syntax and substring. SELECT LENGTH(' ' IN 'Find the position'); uses LENGTH wrongly. SELECT POSITION('space' IN 'Find the position'); searches for word 'space' which is not present. SELECT POSITION(' ' FROM 'Find the position'); uses FROM instead of IN, which is invalid syntax.

3. Final Answer:

   SELECT POSITION(' ' IN 'Find the position'); -> Option A

4. Quick Check:

   POSITION(' ' IN string) finds first space [OK]

Hint: Use POSITION(' ' IN string) to find first space [OK]

Common Mistakes:

• Using LENGTH instead of POSITION
• Searching for word 'space' instead of character
• Using FROM instead of IN in POSITION