Substring and overlay functions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of SUBSTRING with position and length

What is the output of the following SQL query?

SELECT SUBSTRING('PostgreSQL Database', 5, 6);

PostgreSQL

SELECT SUBSTRING('PostgreSQL Database', 5, 6);

A 'greSQL'

B 'greSQL D'

C 'greSQL '

D 'SQL Da'

💡 Hint

Remember SUBSTRING(string, start_position, length) extracts length characters starting at start_position.

The substring starts at position 5 (the 'g' in 'PostgreSQL') and extracts 6 characters: 'greSQL'.

❓ query_result

intermediate

Output of OVERLAY replacing substring

What is the output of this SQL query?

SELECT OVERLAY('Hello World' PLACING 'SQL' FROM 7 FOR 5);

PostgreSQL

SELECT OVERLAY('Hello World' PLACING 'SQL' FROM 7 FOR 5);

A 'Hello SQLld'

B 'Hello SQLd'

C 'Hello SQLrld'

D 'Hello SQL'

💡 Hint

OVERLAY replaces a substring starting at position FROM for length FOR with the PLACING string.

Starting at position 7, it replaces 5 characters ('World') with 'SQL', resulting in 'Hello SQL'.

📝 Syntax

advanced

Identify the syntax error in SUBSTRING usage

Which option contains a syntax error when using the SUBSTRING function in PostgreSQL?

A SELECT SUBSTRING('abcdef' FROM 2 TO 3);

B SELECT SUBSTRING('abcdef', 2, 3);

C SELECT SUBSTRING('abcdef' FROM 2 FOR 3);

D SELECT SUBSTRING('abcdef' FROM 2);

💡 Hint

Check the correct syntax for specifying length in SUBSTRING with FROM and FOR.

The correct syntax uses FROM position FOR length, not TO. Option A uses TO which is invalid.

❓ optimization

advanced

Optimizing string replacement with OVERLAY

You want to replace the first 4 characters of 'Database' with 'Info'. Which query is the most efficient and correct?

A SELECT OVERLAY('Database' PLACING 'Info' FROM 1 FOR 5);

B SELECT OVERLAY('Database' PLACING 'Info' FROM 0 FOR 4);

C SELECT OVERLAY('Database' PLACING 'Info' FROM 1 FOR 4);

D SELECT OVERLAY('Database' PLACING 'Info' FROM 2 FOR 4);

💡 Hint

Positions in strings start at 1 in PostgreSQL.

Option C correctly replaces the first 4 characters starting at position 1 with 'Info'.

🧠 Conceptual

expert

Understanding behavior of SUBSTRING with negative start position

What happens when you run this query?

SELECT SUBSTRING('Example' FROM -3 FOR 2);

PostgreSQL

SELECT SUBSTRING('Example' FROM -3 FOR 2);

A Returns 'am'

B Returns an empty string

C Raises an error

D Returns 'Ex'

💡 Hint

Check how SUBSTRING handles negative start positions in PostgreSQL.

Negative start positions cause SUBSTRING to return an empty string without error.