I have data in table:

I want to split the data into columns. To get below output:

Is any function to get above output.?

CodePudding user response：

One way of doing this is using the much useful PostgreSQL SPLIT_PART function, which allows you to split on a character (in your specific case, the space). As long as you don't need brackets for the last field, you may split on the open bracket and remove the last bracket with the RTRIM function.

SELECT id, 
       SPLIT_PART(question, ' ', 1)             AS questionid,
       SPLIT_PART(question, ' ', 2)             AS question_name,
       RTRIM(SPLIT_PART(question, '[', 2), ']') AS sub_question_name
FROM  tab

Check the demo here.

You can deepen your understanding of these functions on PostgreSQL official documentation related to the string functions.