Why PostgreSQL string functions are powerful - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert the string 'hello' to uppercase using a PostgreSQL function.

PostgreSQL

SELECT [1]('hello');

Drag options to blanks, or click blank then click option'

ATRIM

BLOWER

CINITCAP

DUPPER

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOWER instead of UPPER.

Using TRIM which removes spaces, not changes case.

The UPPER function converts all letters in a string to uppercase.

2fill in blank

medium

Complete the code to find the position of the substring 'cat' in the string 'concatenate'.

PostgreSQL

SELECT POSITION([1] IN 'concatenate');

Drag options to blanks, or click blank then click option'

A'cat'

B'rat'

C'bat'

D'dog'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a substring that does not exist in the string.

Forgetting to put the substring in quotes.

The POSITION function returns the starting position of the substring 'cat' in the string.

3fill in blank

hard

Fix the error in the code to extract the first 4 characters from the string 'PostgreSQL'.

PostgreSQL

SELECT SUBSTRING('PostgreSQL' [1] 4);

Drag options to blanks, or click blank then click option'

ASTART 1 LENGTH

BFOR 4 FROM 1

CFROM 1 FOR

DFROM 4 FOR

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of FOR and FROM.

Using incorrect keywords like START or LENGTH.

The correct syntax for SUBSTRING is SUBSTRING(string FROM start FOR length).

4fill in blank

hard

Fill both blanks to trim spaces from both ends of the string and convert it to lowercase.

PostgreSQL

SELECT [1]([2]('  Hello World  '));

Drag options to blanks, or click blank then click option'

ALOWER

BUPPER

CTRIM

DINITCAP

Attempts:

3 left

💡 Hint

Common Mistakes

Using UPPER instead of LOWER for case conversion.

Not trimming spaces before changing case.

First, TRIM removes spaces from both ends. Then, LOWER converts the string to lowercase.

5fill in blank

hard

Fill all three blanks to create a query that replaces 'day' with 'night' in the string, converts it to uppercase, and trims spaces.

PostgreSQL

SELECT [1]([2](REPLACE('Good day  ', [3], 'night')));

Drag options to blanks, or click blank then click option'

ATRIM

BUPPER

C'day'

DREPLACE

Attempts:

3 left

💡 Hint

Common Mistakes

Putting TRIM inside REPLACE incorrectly.

Not quoting the substring 'day'.

Changing case before replacing.

The REPLACE function swaps 'day' with 'night'. Then UPPER converts to uppercase. Finally, TRIM removes spaces.

Note: The code applies REPLACE first, then UPPER, then TRIM.

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