0

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!

Improve this question
2
  • It doesn't make sense for me. How do you handle correlated subqueries or nested ones(more than 2 levels)? How do you want to differentiate between function call and subquery? Commented Jun 6, 2018 at 7:11
  • Ah of course, for every text in brackets, I want to add a new row with the same id. I have made an edit. Commented Jun 6, 2018 at 7:14

1 Answer 1

Reset to default
2 
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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you!! Can you please help with the new row 55? I've made an edit to the question and required output.
hmmm... thank you so much for your troubles! But it does not give the desired output for 55 as 55, text1 text2 and 55, example4 :( Basically only the text in brackets should be removed not the text infront or before...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.