Why PostgreSQL string functions are powerful - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this string function query?

Consider the following PostgreSQL query that uses the substring function:

SELECT substring('PostgreSQL is powerful' FROM 1 FOR 10) AS result;

What will be the value of result?

PostgreSQL

SELECT substring('PostgreSQL is powerful' FROM 1 FOR 10) AS result;

A 'Postgresq'

B 'PostgreSQL i'

C 'PostgreSQL '

D 'PostgreSQL'

💡 Hint

The substring function extracts a part of the string starting at position 1 for 10 characters.

The substring function extracts exactly 10 characters starting from position 1. 'PostgreSQL' is 10 characters long, so the output is 'PostgreSQL'.

❓ query_result

intermediate

What does this string concatenation query return?

Look at this PostgreSQL query:

SELECT 'Hello' || ' ' || 'World!' AS greeting;

What is the value of greeting?

PostgreSQL

SELECT 'Hello' || ' ' || 'World!' AS greeting;

A 'Hello World!'

B 'Hello World!'

C 'HelloWorld!'

D 'HelloWorld !'

💡 Hint

The || operator joins strings exactly as written.

The || operator concatenates strings. Here, 'Hello', a space ' ', and 'World!' are joined to form 'Hello World!'.

📝 Syntax

advanced

Which option correctly uses the replace function to change 'cat' to 'dog'?

You want to replace all occurrences of 'cat' with 'dog' in the string 'The cat sat on the cat mat'. Which query is correct?

A SELECT replace('The cat sat on the cat mat', 'cat');

B SELECT replace('The cat sat on the cat mat', 'dog', 'cat');

C SELECT replace('The cat sat on the cat mat', 'cat', 'dog');

D SELECT replace('The cat sat on the cat mat', 'cat', 'dog', 'extra');

💡 Hint

The replace function takes exactly three arguments: the string, the substring to find, and the substring to replace with.

Option C correctly calls replace with the string, the substring to find ('cat'), and the substring to replace ('dog').

❓ optimization

advanced

Which query is more efficient to check if a string starts with 'abc'?

You want to find rows where the column code starts with 'abc'. Which query is more efficient in PostgreSQL?

A SELECT * FROM table WHERE code LIKE 'abc%';

B SELECT * FROM table WHERE substring(code FROM 1 FOR 3) = 'abc';

C SELECT * FROM table WHERE position('abc' IN code) = 1;

D SELECT * FROM table WHERE code = 'abc%';

💡 Hint

LIKE with a prefix pattern can use indexes efficiently.

Using LIKE 'abc%' allows PostgreSQL to use an index on the column if available, making it more efficient than substring or position functions.

🧠 Conceptual

expert

Why are PostgreSQL string functions considered powerful compared to basic string operations?

Which of the following reasons best explains why PostgreSQL string functions are powerful?

A They allow complex pattern matching, manipulation, and extraction directly in queries without external processing.

B They only support simple concatenation and trimming, requiring external tools for advanced tasks.

C They are limited to ASCII characters and cannot handle Unicode strings properly.

D They require writing custom functions in other languages for any string manipulation.

💡 Hint

Think about what built-in string functions enable you to do inside the database.

PostgreSQL provides a rich set of built-in string functions that support pattern matching, replacement, extraction, and more, enabling powerful text processing inside SQL queries.

Practice

1. What is the main advantage of using PostgreSQL string functions when working with text data?

easy

A. They automatically create indexes on text columns.

B. They encrypt text data for security.

C. They convert text data into numeric data types.

D. They simplify text manipulation tasks like searching and modifying strings.

Solution

1. Step 1: Understand the purpose of string functions

   PostgreSQL string functions are designed to help manipulate and analyze text data easily.

2. Step 2: Identify the correct advantage

   Among the options, only simplifying text manipulation tasks like searching and modifying strings matches the purpose of string functions.

3. Final Answer:

   They simplify text manipulation tasks like searching and modifying strings. -> Option D

4. Quick Check:

   String functions simplify text tasks = B [OK]

Hint: String functions help change and find text easily [OK]

Common Mistakes:

• Confusing string functions with indexing features
• Thinking string functions convert data types
• Assuming string functions handle encryption

2. Which of the following is the correct syntax to get the length of a string in PostgreSQL?

easy

A. LEN('hello world')

B. STRLEN('hello world')

C. LENGTH('hello world')

D. SIZE('hello world')

Solution

1. Step 1: Recall PostgreSQL string length function

   The correct function to get string length in PostgreSQL is LENGTH().

2. Step 2: Compare options

   Only LENGTH('hello world') matches the correct syntax; others are invalid in PostgreSQL.

3. Final Answer:

   LENGTH('hello world') -> Option C

4. Quick Check:

   LENGTH() gets string length = A [OK]

Hint: Use LENGTH() to find string size in PostgreSQL [OK]

Common Mistakes:

• Using LEN() which is from other SQL dialects
• Using STRLEN() or SIZE() which don't exist in PostgreSQL
• Forgetting to use parentheses

3. What will be the output of this query?

SELECT UPPER(SUBSTRING('PostgreSQL', 1, 4));

medium

A. 'post'

B. 'POST'

C. 'POSTG'

D. 'Post'

Solution

1. Step 1: Understand SUBSTRING function

   SUBSTRING('PostgreSQL', 1, 4) extracts the first 4 characters: 'Post'.

2. Step 2: Apply UPPER function

   UPPER('Post') converts it to uppercase: 'POST'.

3. Final Answer:

   'POST' -> Option B

4. Quick Check:

   UPPER(SUBSTRING()) = 'POST' [OK]

Hint: SUBSTRING extracts; UPPER converts to uppercase [OK]

Common Mistakes:

• Confusing substring length or start position
• Ignoring case conversion by UPPER()
• Assuming SUBSTRING returns more characters

4. Identify the error in this PostgreSQL query:

SELECT CONCAT('Hello', 123);

medium

A. The query is correct and will run without error.

B. Missing space between arguments in CONCAT.

C. CONCAT cannot combine strings and numbers directly.

D. CONCAT requires at least three arguments.

Solution

1. Step 1: Check CONCAT function behavior

   CONCAT in PostgreSQL automatically converts non-string types to text.

2. Step 2: Analyze the query

   CONCAT('Hello', 123) concatenates 'Hello' and '123' as 'Hello123' without error.

3. Final Answer:

   The query is correct and will run without error. -> Option A

4. Quick Check:

   CONCAT auto-converts types = C [OK]

Hint: CONCAT auto-converts numbers to text [OK]

Common Mistakes:

• Assuming CONCAT needs only strings
• Thinking CONCAT requires spaces between arguments
• Believing CONCAT needs 3+ arguments

5. You want to extract the domain name from email addresses stored in a column email. Which PostgreSQL string function combination is best to get the part after '@'?

hard

A. SUBSTRING(email FROM POSITION('@' IN email) + 1)

B. LEFT(email, POSITION('@' IN email))

C. RIGHT(email, LENGTH(email) - POSITION('@' IN email) - 1)

D. TRIM(email, '@')

Solution

1. Step 1: Understand the goal

   We want the text after '@' in the email string.

2. Step 2: Analyze each option

   SUBSTRING(email FROM POSITION('@' IN email) + 1) uses SUBSTRING starting just after '@', correctly extracting the domain.
   LEFT(email, POSITION('@' IN email)) extracts left part including '@', not domain.
   RIGHT(email, LENGTH(email) - POSITION('@' IN email) - 1) correctly calculates length for RIGHT to get domain.
   TRIM(email, '@') trims '@' characters from ends, not extracting domain.

3. Final Answer:

   SUBSTRING(email FROM POSITION('@' IN email) + 1) -> Option A

4. Quick Check:

   SUBSTRING after '@' = domain [OK]

Hint: Use SUBSTRING from position after '@' to get domain [OK]

Common Mistakes:

• Using LEFT which gets text before '@'
• Miscalculating length for RIGHT function
• Using TRIM which removes characters only at ends