String length and position functions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find the length of a string

What is the output of the following SQL query in PostgreSQL?

PostgreSQL

SELECT LENGTH('Hello, World!') AS length;

A Error

B 12

C 13

D 14

💡 Hint

The LENGTH function counts all characters including punctuation and spaces.

The string 'Hello, World!' has 13 characters including the comma and space.

❓ query_result

intermediate

Find position of a substring

What is the result of this query?

PostgreSQL

SELECT POSITION('cat' IN 'concatenate') AS pos;

A 4

B 3

C 1

D 0

💡 Hint

POSITION returns the starting index of the substring if found, else 0.

The substring 'cat' starts at the 4th character in 'concatenate'.

📝 Syntax

advanced

Identify the syntax error in string length query

Which option will cause a syntax error when trying to get the length of a string in PostgreSQL?

A SELECT LENGTH 'test string';

B SELECT LENGTH('test string');

C SELECT LENGTH('test string') AS len;

D SELECT LENGTH('test string')

💡 Hint

Check if the function call syntax is correct with parentheses.

Option A misses parentheses around the string argument, causing syntax error.

❓ query_result

advanced

Find position of substring not present

What does this query return?

PostgreSQL

SELECT POSITION('dog' IN 'concatenate') AS pos;

A 1

B 0

C NULL

D Error

💡 Hint

POSITION returns 0 if substring is not found.

Since 'dog' is not in 'concatenate', POSITION returns 0.

❓ optimization

expert

Optimize query to find length of trimmed string

Which query efficiently returns the length of a string after removing spaces from both ends?

A SELECT LENGTH(REPLACE(' example ', ' ', '')) AS len;

B SELECT LENGTH(' example ') - 2 AS len;

C SELECT LENGTH(LTRIM(RTRIM(' example '))) AS len;

D SELECT LENGTH(TRIM(' example ')) AS len;

💡 Hint

TRIM removes spaces from both ends in one step.

Option D uses TRIM which removes spaces from both ends efficiently and then measures length.