純 SQL 解析特定格式字串：輔助數字序列表法

問題場景

資料庫欄位儲存逗號分隔的多值字串，如 "SYSIBM","SYSFUN","SYSPROC","STOLZE"，需解析為單行單值：

"SYSIBM"
"SYSFUN"
"SYSPROC"
"STOLZE"

核心思路：笛卡兒積 + 序列表

1. 建立**輔助數字序列表** numSerial(id INT PK)，內含 1..N 連續整數（N ≥ 最大字串長度）。
2. 目標表 strings(id PK, str VARCHAR) 與 numSerial 交叉聯接 → 每字元一行：(string_id, pos, char)。
3. 識別分隔符位置，推算各元素起止位置。
4. 用 SUBSTR 切分元素，彙總輸出。

完整 SQL 範例

-- 1. 建立序列表 (一次性)
CREATE TABLE numSerial (id INT NOT NULL PRIMARY KEY);
-- 插入 1..10000 (或用遞迴 CTE 動態產生)

-- 2. 範例資料
CREATE TABLE strings (id INT PK, str VARCHAR(128));
INSERT INTO strings VALUES 
(1,'ab,c,d,123'), (2,'123,456789,abc,123'), (3,'a,b,c,d'), (4,'string');

-- 3. 解析查詢 (DB2 標準 SQL)
WITH char_pos AS (
  SELECT s.id, s.str, n.id AS pos,
         SUBSTR(s.str, n.id, 1) AS ch
  FROM strings s
  JOIN numSerial n ON n.id BETWEEN 1 AND LENGTH(s.str)
),
delim AS (
  SELECT id, pos,
         CASE WHEN ch = ',' THEN 1 ELSE 0 END AS is_delim
  FROM char_pos
),
groups AS (
  SELECT id, pos, ch,
         SUM(is_delim) OVER (PARTITION BY id ORDER BY pos ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
  FROM char_pos
  JOIN delim USING (id, pos)
),
elements AS (
  SELECT id, grp,
         LISTAGG(ch, '') WITHIN GROUP (ORDER BY pos) AS elem
  FROM groups
  WHERE ch <> ','
  GROUP BY id, grp
)
SELECT id, elem FROM elements ORDER BY id, grp;

關鍵技術點

• 純標準 SQL：無需 UDF/預存程序，跨資料庫可移植 (需支援 Window Function & LISTAGG)。
• 輔助表一次建立，永久復用：解析、拆行、生成序列、時間維度等通用場景。
• 避免遞迴/程式化邏輯**：集合思維，效能穩定，優化器友善。

衍生應用

• CSV 匯入預處理
• 日誌欄位結構化
• 多值屬性正規化 (EAV 模型轉換)