How can I add brackets to each word of a string which contains multiple words in SQL?

Question

How can I add brackets to each word of a string which contains multiple words in SQL? For example, I have:

string example = 'word1,word2,word3,word4,word5'

How can I convert it to something like this:

string example = '[word1],[word2],[word3],[word4],[word5]'

check this: https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql — Ibrahim Hammed, Sep 26 '22 at 09:44
What have you tried so far? Why not replace all occurences of a comma with `],[`, append a `[`, and prepend a `]`? — Nico Haase, Sep 26 '22 at 09:44

score 3 · Accepted Answer · answered Sep 26 '22 at 09:46

We can use a combination of REPLACE() and string concatenation:

SELECT val, CONCAT('[', REPLACE(val, ',', '],[') , ']') AS output
FROM yourTable;

On MySQL 8+, we can use a regex replacement:

SELECT val, REGEXP_REPLACE(val, '([^,]+)', '[$1]') AS output
FROM yourTable;

1 Answers1