String to array and array to string in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Convert a comma-separated string to an array

What is the result of this PostgreSQL query that converts a string to an array?

PostgreSQL

SELECT string_to_array('apple,banana,cherry', ',');

A {apple,banana,cherry}

B ['apple','banana','cherry']

C apple,banana,cherry

D ARRAY['apple','banana','cherry']

💡 Hint

Use the string_to_array function with the delimiter as a comma.

The string_to_array function splits the string by the delimiter and returns a PostgreSQL array shown with curly braces.

❓ query_result

intermediate

Convert an array to a string with a delimiter

What does this PostgreSQL query return when converting an array to a string separated by semicolons?

PostgreSQL

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

A red;green;blue

B {red;green;blue}

C ARRAY['red;green;blue']

D red,green,blue

💡 Hint

array_to_string joins array elements using the given delimiter.

array_to_string returns a single string with array elements joined by the delimiter.

📝 Syntax

advanced

Identify the syntax error in string to array conversion

Which option contains a syntax error when trying to convert a string to an array in PostgreSQL?

PostgreSQL

SELECT string_to_array('dog|cat|mouse', '|');

A SELECT string_to_array('dog|cat|mouse', '|');

B ;)'|' ,'esuom|tac|god'(yarra_ot_gnirts TCELES

C SELECT string_to_array('dog|cat|mouse' '|');

D ELECT string_to_array('dog|cat|mouse', '|');

💡 Hint

Check for missing commas between function arguments.

Option C is missing a comma between the string and delimiter arguments, causing a syntax error.

❓ query_result

advanced

Using array_to_string with NULL elements

What is the output of this query that converts an array with NULL elements to a string?

PostgreSQL

SELECT array_to_string(ARRAY['a', NULL, 'c'], ',');

A a,c

B a,,c

C a,NULL,c

D a,,NULL,c

💡 Hint

NULL elements become empty strings in the output.

array_to_string replaces NULL elements with empty strings, so two commas appear with nothing between.

🧠 Conceptual

expert

Choosing the correct function for string to array conversion with multiple delimiters

You have a string 'one;two,three|four' and want to split it into an array using any of the delimiters ';', ',', or '|'. Which PostgreSQL approach correctly achieves this?

A Use array_to_string('one;two,three|four', '[;,|]')

B Use string_to_array('one;two,three|four', ';,|')

C Use split_part('one;two,three|four', '[;,|]', 1)

D Use regexp_split_to_array('one;two,three|four', '[;,|]')

💡 Hint

string_to_array only accepts a single delimiter character, not multiple.

regexp_split_to_array allows splitting by a regular expression matching multiple delimiters, unlike string_to_array.