String to array and array to string in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to convert a comma-separated string into an array.

PostgreSQL

SELECT string_to_array('apple,banana,cherry', [1]) AS fruits_array;

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

A';'

B'.'

C' '

D','

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong delimiter like ';' or '.' which won't split the string correctly.

The string_to_array function splits the string by the delimiter. Here, the delimiter is a comma ',' to separate the fruits.

2fill in blank

medium

Complete the code to join an array of strings into a single string separated by semicolons.

PostgreSQL

SELECT array_to_string(ARRAY['red', 'green', 'blue'], [1]) AS colors_string;

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

A' '

B';'

C','

D':'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a comma ',' instead of a semicolon ';' which changes the output format.

The array_to_string function joins array elements using the specified delimiter. Here, ';' is used to separate colors.

3fill in blank

hard

Fix the error in the code to correctly convert a string to an array using a space as delimiter.

PostgreSQL

SELECT string_to_array('one two three', [1]) AS words_array;

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

A' '

B','

C';'

D''

Attempts:

3 left

💡 Hint

Common Mistakes

Using an empty string '' as delimiter which won't split the string correctly.

Using a comma ',' which does not split the string correctly.

To split the string by spaces, the delimiter must be a space character ' '.

4fill in blank

hard

Fill both blanks to convert a string to an array and then back to a string with a different delimiter.

PostgreSQL

SELECT array_to_string(string_to_array('cat|dog|bird', [1]), [2]) AS new_string;

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

A'|'

B','

C';'

D' '

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong delimiters that do not match the original string or desired output.

First, split the string by '|' to get the array. Then join the array elements with ';' to form the new string.

5fill in blank

hard

Fill all three blanks to create an array from a string, filter elements containing 'a', and join them with commas.

PostgreSQL

SELECT array_to_string(ARRAY(SELECT * FROM unnest(string_to_array('apple,banana,cherry,date', [1])) WHERE [2] LIKE '%a%'), [3]) AS filtered_string;

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

A','

Bunnest

D';'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong delimiters or missing unnest function causing errors.

Split the string by ',' to get the array. Use unnest to expand array elements. Filter elements containing 'a'. Join filtered elements with ','.

Practice

1. What does the PostgreSQL function string_to_array('apple,banana,cherry', ',') return?

easy

A. An array with one element containing the whole string

B. A single string 'apple,banana,cherry'

C. An array with elements ['apple', 'banana', 'cherry']

D. An error because the delimiter is missing

Solution

1. Step 1: Understand string_to_array function

   This function splits a string into an array using the given delimiter.

2. Step 2: Apply the delimiter ',' to the string

   The string 'apple,banana,cherry' is split at each comma, creating ['apple', 'banana', 'cherry'].

3. Final Answer:

   An array with elements ['apple', 'banana', 'cherry'] -> Option C

4. Quick Check:

   string_to_array splits string by delimiter [OK]

Hint: string_to_array splits text by delimiter into array [OK]

Common Mistakes:

• Thinking it returns a string instead of an array
• Using wrong delimiter causing no split
• Confusing array_to_string with string_to_array

2. Which of the following is the correct syntax to convert an array ARRAY['red','green','blue'] into a string separated by semicolons in PostgreSQL?

easy

A. array_to_string(ARRAY['red','green','blue'], ';')

B. string_to_array(ARRAY['red','green','blue'], ';')

C. array_join(ARRAY['red','green','blue'], ';')

D. join_array(ARRAY['red','green','blue'], ';')

Solution

1. Step 1: Identify function to join array elements

   PostgreSQL uses array_to_string to join array elements into a string with a delimiter.

2. Step 2: Check syntax correctness

   array_to_string(array, delimiter) is correct; other options are invalid functions.

3. Final Answer:

   array_to_string(ARRAY['red','green','blue'], ';') -> Option A

4. Quick Check:

   array_to_string joins array with delimiter [OK]

Hint: Use array_to_string(array, delimiter) to join array elements [OK]

Common Mistakes:

• Using string_to_array instead of array_to_string
• Using non-existent functions like array_join
• Incorrect order of arguments

3. What is the output of this query?

SELECT array_to_string(string_to_array('dog|cat|bird', '|'), ', ');

medium

A. NULL

B. 'dog|cat|bird'

C. 'dogcatbird'

D. 'dog, cat, bird'

Solution

1. Step 1: Apply string_to_array with '|' delimiter

   The string 'dog|cat|bird' is split into array ['dog', 'cat', 'bird'].

2. Step 2: Use array_to_string with ', ' delimiter

   The array elements are joined with ', ' producing 'dog, cat, bird'.

3. Final Answer:

   'dog, cat, bird' -> Option D

4. Quick Check:

   string_to_array then array_to_string changes delimiter [OK]

Hint: Chain string_to_array and array_to_string to change delimiters [OK]

Common Mistakes:

• Expecting original string unchanged
• Missing spaces after commas in output
• Confusing delimiters in functions

4. Identify the error in this query:

SELECT array_to_string(string_to_array('one,two,three'), ',');

medium

A. array_to_string requires two arguments

B. Missing delimiter argument in string_to_array

C. string_to_array cannot be nested inside array_to_string

D. No error, query is correct

Solution

1. Step 1: Check string_to_array usage

   string_to_array requires two arguments: the string and the delimiter.

2. Step 2: Identify missing delimiter

   The query misses the delimiter argument in string_to_array, causing a syntax error.

3. Final Answer:

   Missing delimiter argument in string_to_array -> Option B

4. Quick Check:

   string_to_array needs delimiter [OK]

Hint: Always provide delimiter to string_to_array [OK]

Common Mistakes:

• Omitting delimiter in string_to_array
• Thinking array_to_string needs more arguments
• Assuming nesting functions causes error

5. You have a column tags storing comma-separated strings like 'red,blue,green'. You want to find rows where the tag 'blue' exists. Which query correctly uses string_to_array and array_to_string to help filter these rows?

hard

A. SELECT * FROM table WHERE 'blue' = ANY(string_to_array(tags, ','));

B. SELECT * FROM table WHERE array_to_string(tags, ',') LIKE '%blue%';

C. SELECT * FROM table WHERE 'blue' = string_to_array(tags, ',');

D. SELECT * FROM table WHERE 'blue' IN array_to_string(tags, ',');

Solution

1. Step 1: Convert tags string to array

   Use string_to_array(tags, ',') to split tags into an array.

2. Step 2: Check if 'blue' is in the array

   Use 'blue' = ANY(array) to test if 'blue' exists in the array.

3. Final Answer:

   SELECT * FROM table WHERE 'blue' = ANY(string_to_array(tags, ',')); -> Option A

4. Quick Check:

   Use string_to_array + ANY to filter arrays [OK]

Hint: Use 'value' = ANY(string_to_array(column, delimiter)) to filter [OK]

Common Mistakes:

• Using array_to_string to filter arrays
• Comparing string to array directly
• Using IN with array_to_string incorrectly