通過分隔符將多列拆分為多列

我想通過分隔符將多列拆分為多列。我正在使用 SQLite，但如有必要，我願意導入另一個 DBMS。

---

一個範例表，顯示該表的目前狀態，其中“;” 是分隔符：

http://sqlfiddle.com/#!5/f1845

---

一個範例表，顯示了我希望更新後的表的外觀：

http://sqlfiddle.com/#!5/d6283f

在 SQLite 中，你必須用instr()和 substr()函式來做這件事：

SELECT before_semicolon,
      substr(rest, 1, pos - 1) AS after_semicolon1,
      substr(rest, pos + 1)    AS after_semicolon2
FROM (SELECT before_semicolon,
            rest,
            instr(rest || ';', ';') AS pos
     FROM (SELECT substr(data1, 1, pos - 1) AS before_semicolon,
                  substr(data1, pos + 1)    AS rest
           FROM (SELECT data1, instr(data1 || ';', ';') AS pos
                 FROM example)));

創建一個像 Postgres 的使用者定義函式可能是一個更好的主意split_part()。

您說您也對其他 DBMS 解決方案持開放態度。

這在 Postgres 中很容易：

select id, 
      split_part(data1, ';', 1) as Data1_Before_Semicolon, 
      split_part(data1, ';', 2) as Data1_After_Semicolon1, 
      split_part(data2, ';', 1) as Data2_Before_Semicolon, 
      split_part(data2, ';', 2) as Data2_After_Semicolon1, 
      split_part(data2, ';', 3) as Data2_After_Semicolon2,
      split_part(data3, ';', 1) as Data3_Before_Semicolon, 
      split_part(data3, ';', 2) as Data3_After_Semicolon1, 
      split_part(data3, ';', 3) as Data3_After_Semicolon2,
      split_part(data4, ';', 1) as Data4_Before_Semicolon, 
      split_part(data4, ';', 2) as Data4_After_Semicolon1, 
      split_part(data4, ';', 3) as Data4_After_Semicolon2,
      split_part(data5, ';', 1) as Data5_Before_Semicolon, 
      split_part(data5, ';', 2) as Data5_After_Semicolon1, 
      split_part(data5, ';', 3) as Data5_After_Semicolon2
from example; 

線上範例：https ://rextester.com/RSNO8845

要更新它，請使用相同的表達式：

update the table
  set Data1_Before_Semicolon = split_part(data1, ';', 1, 
      Data1_After_Semicolon1 = split_part(data1, ';', 2)
       ....

引用自：https://dba.stackexchange.com/questions/241473