2024年04月02日玄貓（BlackCat）

SQL 字串處理技巧解析與排序

本文探討 SQL 字串處理技巧，包含如何解析逗號分隔值以及對字串中字元排序。針對不同資料函式庫系統（DB2、MySQL、Oracle、PostgreSQL、SQL Server）提供對應解決方案，並深入解析程式邏輯、設計考量以及效能最佳化策略，輔以 Plantuml 流程圖，幫助讀者快速理解並應用於實務。

資料函式庫 SQL

SQL 字串處理資料函式庫解析排序效能調校

在資料函式庫應用中，字串處理是常見的需求，高效的字串操作能顯著提升資料函式庫效能。本文將探討兩種常見的字串處理場景：解析逗號分隔值和字元排序，並針對不同資料函式庫系統提供最佳實務解法。首先，解析逗號分隔值通常應用於將多個值傳遞給 SQL 查詢，例如 IN 列表。其次，字元排序則常用於資料清理、格式化以及特定查詢需求。不同資料函式庫系統提供的字串函式各有差異，選擇合適的函式對於效能至關重要。

將分隔資料轉換為多值 IN 列表

問題描述

假設你有一串以分隔符號分隔的資料，並且想要將這些資料傳遞給 WHERE 子句中的 IN 列表迭代器。考慮以下字串：

7654,7698,7782,7788

你希望在 WHERE 子句中使用這個字串，但是以下的 SQL 陳述式會失敗，因為 EMPNO 是數值欄位：

SELECT ename, sal, deptno
FROM emp
WHERE empno IN ('7654,7698,7782,7788')

這個 SQL 陳述式會失敗，因為雖然 EMPNO 是數值欄位，但是 IN 列表是由單一字串值組成的。你希望這個字串被視為逗號分隔的數值列表。

解決方案

表面上看，似乎 SQL 應該能夠自動將分隔字串視為多個分隔值的列表，但事實並非如此。當遇到引號內的逗號時，SQL 無法知道它代表多值列表。SQL 必須將引號內的所有內容視為單一實體，即單一字串值。你必須將字串分解為個別的 EMPNO 值。這個解決方案的關鍵是遍歷字串，但不是遍歷到個別的字元。你希望將字串遍歷到有效的 EMPNO 值。

DB2 解決方案

透過遍歷傳遞給 IN 列表的字串，可以輕鬆地將其轉換為多行。ROW_NUMBER、LOCATE 和 SUBSTR 函式在此處特別有用：

SELECT empno, ename, sal, deptno
FROM emp
WHERE empno IN (
  SELECT CAST(SUBSTR(c, 2, LOCATE(',', c, 2) - 2) AS INTEGER) AS empno
  FROM (
    SELECT SUBSTR(csv.emps, CAST(iter.pos AS INTEGER)) AS c
    FROM (
      SELECT ',' || '7654,7698,7782,7788' || ',' AS emps
      FROM t1
    ) csv,
    (
      SELECT id AS pos
      FROM t100
    ) iter
    WHERE iter.pos <= LENGTH(csv.emps)
  ) x
  WHERE LENGTH(c) > 1 AND SUBSTR(c, 1, 1) = ','
)

MySQL 解決方案

透過遍歷傳遞給 IN 列表的字串，可以輕鬆地將其轉換為多行：

SELECT empno, ename, sal, deptno
FROM emp
WHERE empno IN (
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(list.vals, ',', iter.pos), ',', -1) AS empno
  FROM (
    SELECT id AS pos
    FROM t10
  ) AS iter,
  (
    SELECT '7654,7698,7782,7788' AS vals
    FROM t1
  ) list
  WHERE iter.pos <= (LENGTH(list.vals) - LENGTH(REPLACE(list.vals, ',', ''))) + 1
)

Oracle 解決方案

透過遍歷傳遞給 IN 列表的字串，可以輕鬆地將其轉換為多行。ROWNUM、SUBSTR 和 INSTR 函式在此處特別有用：

SELECT empno, ename, sal, deptno
FROM emp
WHERE empno IN (
  SELECT TO_NUMBER(RTRIM(SUBSTR(emps, INSTR(emps, ',', 1, iter.pos) + 1, INSTR(emps, ',', 1, iter.pos + 1) - INSTR(emps, ',', 1, iter.pos)), ',')) AS emps
  FROM (
    SELECT ',' || '7654,7698,7782,7788' || ',' AS emps
    FROM t1
  ) csv,
  (
    SELECT ROWNUM AS pos
    FROM emp
  ) iter
  WHERE iter.pos <= ((LENGTH(csv.emps) - LENGTH(REPLACE(csv.emps, ','))) / LENGTH(',')) - 1
)

PostgreSQL 解決方案

透過遍歷傳遞給 IN 列表的字串，可以輕鬆地將其轉換為多行。SPLIT_PART 函式使得解析字串變得容易：

SELECT ename, sal, deptno
FROM emp
WHERE empno IN (
  SELECT CAST(empno AS INTEGER) AS empno
  FROM (
    SELECT SPLIT_PART(list.vals, ',', iter.pos) AS empno
    FROM (
      SELECT id AS pos
      FROM t10
    ) iter,
    (
      SELECT ',' || '7654,7698,7782,7788' || ',' AS vals
      FROM t1
    ) list
    WHERE iter.pos <= LENGTH(list.vals) - LENGTH(REPLACE(list.vals, ',', ''))
  ) z
  WHERE LENGTH(empno) > 0
)

SQL Server 解決方案

透過遍歷傳遞給 IN 列表的字串，可以輕鬆地將其轉換為多行。ROW_NUMBER、CHARINDEX 和 SUBSTRING 函式在此處特別有用：

SELECT empno, ename, sal, deptno
FROM emp
WHERE empno IN (
  SELECT SUBSTRING(c, 2, CHARINDEX(',', c, 2) - 2) AS empno
  FROM (
    SELECT SUBSTRING(csv.emps, iter.pos, LEN(csv.emps)) AS c
    FROM (
      SELECT ',' + '7654,7698,7782,7788' + ',' AS emps
      FROM t1
    ) csv,
    (
      SELECT id AS pos
      FROM t100
    ) iter
    WHERE iter.pos <= LEN(csv.emps)
  ) x
  WHERE LEN(c) > 1 AND SUBSTRING(c, 1, 1) = ','
)

詳細解析

程式邏輯與設計考量

這個解決方案的第一步，也是最重要的一步，是遍歷字串。一旦完成這一步，剩下的就是使用你的 DBMS 所提供的函式將字串解析為個別的數值。

對於 DB2 和 SQL Server，內聯檢視 X（第6-11行）遍歷了字串。這個解決方案的想法是“遍歷”字串，以便每一行都比前一行少一個字元。

使用 Plantuml 圖表解釋流程

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL 字串處理技巧解析與排序

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖表呈現瞭解決方案的流程。首先，初始化包含分隔資料的字串。接著，遍歷這個字串，將其分解成個別的值。然後，解析這些值以取得所需的資料。最後，使用這些解析結果來查詢所需的資料。

字串處理技巧：解析與排序字元

在資料函式倉管理中，經常需要處理字串資料，無論是解析逗號分隔的值還是對字串中的字元進行排序。本文將介紹不同資料函式庫系統中如何實作這些功能。

解析逗號分隔的值

許多情況下，資料函式庫中的某個欄位包含逗號分隔的值。以下介紹如何在不同資料函式庫系統中解析這些值。

Oracle

在Oracle中，可以使用SUBSTR和INSTR函式來解析字串。首先，需要產生一個序列來遍歷字串中的每個值。

SELECT SUBSTR(emps,
               INSTR(emps,',',1,iter.pos)+1,
               INSTR(emps,',',1,iter.pos+1) - INSTR(emps,',',1,iter.pos)) AS empno
FROM (SELECT ','||'7654,7698,7782,7788'||',' AS emps FROM t1) csv,
     (SELECT ROWNUM AS pos FROM emp) iter
WHERE iter.pos <= ((LENGTH(csv.emps)-LENGTH(REPLACE(csv.emps,',')))/LENGTH(','))-1;

內容解密：

產生序列：使用(SELECT ROWNUM AS pos FROM emp)來產生一個序列，用於遍歷字串中的每個逗號分隔的值。
解析字串：使用SUBSTR和INSTR函式來提取每個值。INSTR(emps,',',1,iter.pos)找到第iter.pos個逗號的位置，而INSTR(emps,',',1,iter.pos+1)找到第iter.pos+1個逗號的位置。兩者之差即為當前值的長度。
條件篩選：使用WHERE子句來確保只遍歷到有效的逗號分隔值。

PostgreSQL

在PostgreSQL中，可以使用SPLIT_PART函式來簡化解析過程。

SELECT SPLIT_PART(list.vals,',',iter.pos) AS empno
FROM (SELECT id AS pos FROM t10) iter,
     (SELECT ','||'7654,7698,7782,7788'||',' AS vals FROM t1) list
WHERE iter.pos <= LENGTH(list.vals)-LENGTH(REPLACE(list.vals,',',''));

內容解密：

使用SPLIT_PART：該函式直接根據指定的分隔符（逗號）和位置（iter.pos）提取值。
遍歷控制：透過比較原始字串與移除逗號後的字串長度差來控制遍歷次數。

對字串中的字元進行排序

有時，需要對字串中的字元進行排序。以下展示如何在不同資料函式庫系統中實作這一功能。

DB2 和 PostgreSQL

這兩個資料函式庫系統都支援使用聚合函式對字元進行排序。

SELECT ename, STRING_AGG(c, '' ORDER BY c) AS new_name
FROM (
  SELECT a.ename, SUBSTR(a.ename, iter.pos, 1) AS c
  FROM emp a,
       (SELECT id AS pos FROM t10) iter
  WHERE iter.pos <= LENGTH(a.ename)
) x
GROUP BY ename;

內容解密：

拆分字元：內部查詢將每個名字拆分成單個字元。
排序和聚合：使用STRING_AGG函式按字母順序聚合這些字元。

SQL Server

在SQL Server 2017及以後版本中，可以使用STRING_AGG函式。對於早期版本，則需要使用其他方法，如使用MAX和CASE陳述式來模擬。

SELECT ename,
       MAX(CASE WHEN pos=1 THEN c ELSE '' END) +
       MAX(CASE WHEN pos=2 THEN c ELSE '' END) +
       MAX(CASE WHEN pos=3 THEN c ELSE '' END) +
       MAX(CASE WHEN pos=4 THEN c ELSE '' END) +
       MAX(CASE WHEN pos=5 THEN c ELSE '' END) AS new_name
FROM (
  SELECT e.ename,
         SUBSTRING(e.ename, iter.pos, 1) AS c,
         ROW_NUMBER() OVER (PARTITION BY e.ename ORDER BY SUBSTRING(e.ename, iter.pos, 1)) AS pos
  FROM emp e,
       (SELECT ROW_NUMBER() OVER (ORDER BY ename) AS pos FROM emp) iter
  WHERE iter.pos <= LEN(e.ename)
) x
GROUP BY ename;

內容解密：

拆分和排序：內部查詢拆分名字並對字元進行排序。
聚合：外部查詢使用條件聚合來重建排序後的字串。

6.13 識別可視為數字的字串

問題描述

資料函式庫中某個欄位定義為字元資料型別，但實際儲存的資料卻是數值和字元混雜的內容。以下是一個示例檢視表 V：

create view V as
select replace(mixed,' ','') as mixed
from (
  select substr(ename,1,2)||
         cast(deptno as char(4))||
         substr(ename,3,2) as mixed
  from emp
  where deptno = 10
  union all
  select cast(empno as char(4)) as mixed
  from emp
  where deptno = 20
  union all
  select ename as mixed
  from emp
  where deptno = 30
) x

select * from v

執行結果如下：

MIXED
---
-
---
-
---
---
CL10AR
KI10NG
MI10LL
7369
7566
7788
7876
7902
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

我們的目標是篩選出純數值或至少包含數值的資料列。如果資料列中數值與字元混雜，則需要移除字元部分，只保留數值部分。預期的結果如下：

MIXED
---
-
---
-
10
10
10
7369
7566
7788
7876
7902

解決方案

DB2

利用 TRANSLATE、REPLACE 和 POSSTR 函式來隔離並提取數值部分。由於在建立檢視表 V 時涉及型別轉換，需要使用 CAST 函式避免錯誤。

select mixed old,
       cast(
         case
           when replace(translate(mixed,'9999999999','0123456789'),'9','') = ''
           then mixed
           else replace(
                  translate(mixed,
                            repeat('#',length(mixed)),
                            replace(translate(mixed,'9999999999','0123456789'),'9','')),
                  '#','')
         end as integer ) mixed
from V
where posstr(translate(mixed,'9999999999','0123456789'),'9') > 0

MySQL

由於 MySQL 不支援 TRANSLATE 函式，因此需要逐字元檢查。先定義檢視表 V，並使用 CONCAT 和 REPLACE 處理資料。

create view V as 
select concat(
         substr(ename,1,2),
         replace(cast(deptno as char(4)),' ',''),
         substr(ename,3,2)
       ) as mixed 
from emp 
where deptno = 10 
union all 
select replace(cast(empno as char(4)), ' ', '') 
from emp where deptno = 20 
union all 
select ename from emp where deptno = 30;

查詢陳述式如下：

select cast(group_concat(c order by pos separator '') as unsigned) as MIXED1 
from (
  select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c 
  from V, 
       ( select id pos from t10 ) iter 
  where iter.pos <= length(v.mixed) 
    and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57 
) y 
group by mixed 
order by 1;

Oracle

使用 TRANSLATE、REPLACE 和 INSTR 函式隔離數值部分。在建立檢視表 V 時，可以選擇是否使用 CAST。

select to_number (
         case 
           when replace(translate(mixed,'0123456789','9999999999'),'9') is not null 
           then replace(
                  translate(mixed,
                            replace(translate(mixed,'0123456789','9999999999'),'9'),
                            rpad('#',length(mixed),'#')),
                  '#')
           else mixed 
         end 
       ) mixed 
from V 
where instr(translate(mixed,'0123456789','9999999999'),'9') > 0;

#### 方法解析：

DB2 與 Oracle 方法：利用 TRANSLATE 將所有數字轉換為特定字元（如 ‘9’），再透過 REPLACE 將這些 ‘9’ 清除。若結果為空字串，表示該字串為純數字。否則，進一步處理以提取數字。
MySQL 方法：由於不支援 TRANSLATE，採用逐字元檢查的方式，利用 ASCII 碼範圍（48-57）判斷是否為數字，並使用 GROUP_CONCAT 組合結果。