CODEWITHSUNDEEP
sqlpostgresqlsplit
Jay
Jay

Reputation: 359

SQL convert single column to multiple row based on string pattern

I have a table with the following sample data:

id, query
----------
25, normal query
25, query with brackets (example1) 
46, brackets in query (example2) (example3)
55, text1 (example4) text2

For the queries having brackets, I would like to add a row to the table with the information in every bracket as follows:

id, query
----------
25, normal query
25, query with brackets
25, example1
46, brackets in query
46, example2
46, example3
55, text1 text2
55, example4

I can easily identify the rows having brackets with LIKE '%(%)%', however I'm not sure how to split and add new rows.

Thanks!

Upvotes: 0

Views: 68

Answers (1)

Wei Lin
Wei Lin

Reputation: 3811

select 
  id,
  regexp_split_to_table(
    replace(query,')','')
  ,E'\\(') query
from TestTable

Result:

| id |                query |
|----|----------------------|
| 25 |         normal query |
| 25 | query with brackets  |
| 25 |             example1 |
| 46 |   brackets in query  |
| 46 |            example2  |
| 46 |             example3 |

SQL Fiddle Demo Link

For New Question :

Thank you!! Can you please help with the new row 55? I've made an edit to the question and required output.

select 
  id,
  regexp_split_to_table(
    replace(
      replace(
        replace(
          query
          ,') (','(')        
        ,') ','( ')
    ,')','')
  ,E'\\(') query
from TestTable

Result:

| id |                query |
|----|----------------------|
| 25 |         normal query |
| 25 | query with brackets  |
| 25 |             example1 |
| 46 |   brackets in query  |
| 46 |             example2 |
| 46 |             example3 |
| 55 |               text1  |
| 55 |             example4 |
| 55 |                text2 |

SQL Fiddle Demo Link

Upvotes: 2

Related Questions