SQL根据字符串模式将单列转换为多行

Question

我有一个包含以下示例数据的表格:

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

对于有括号的查询，我想在表中添加一行，每个括号中的信息如下:

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

我可以使用 LIKE '%(%)%' 轻松识别带有方括号的行，但是我不确定如何拆分和添加新行。

谢谢!

Answer 1

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

---

结果:

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

---

SQL Fiddle Demo Link

---

对于新问题:

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

---

结果:

| 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