需要在蜂巢中用多个分隔符将列分隔为多个行。

Question

这是我原来的桌子。我需要分隔列段。我在下面展示了我想要的。

我确实尝试过后来看到爆炸，但不是像ABC-DEF那样的字符串，它给了我A，B，C，-，D，……一排排。

​

<table border="1">
<caption>What I Have</caption>
  <tr>
    <th>Unique-Key </th>
    <th>PNR </th>
    <th>Segments </th>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>ABC-DEF;GHI-JKL| JKL-GHI;DEF-ABC</td>
  </tr>
</table>




<table border="1">
<caption>What I want</caption>
  <tr>
    <th>Unique-Key </th>
    <th>PNR </th>
    <th> New Segments </th>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>ABC-DEF</td>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>GHI-JKL</td>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>JKL-GHI</td>
  </tr>
    <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>DEF-ABC</td>
  </tr>
</table>

​

Answer 1

with t as (select 'ABC-DEF;GHI-JKL| JKL-GHI;DEF-ABC' as col)

select  e.col as segments

from    t lateral view explode (split(t.col,'\\s*[;|]\\s*')) e
;

+----------+
| segments |
+----------+
| ABC-DEF  |
| GHI-JKL  |
| JKL-GHI  |
| DEF-ABC  |
+----------+