資料函式庫字串數字擷取技術解析

在資料函式庫應用中，從字串欄位擷取數字的需求相當常見。不同資料函式庫系統提供的字串函式各異，需要根據實際情況選擇合適的方案。例如，PostgreSQL 的 TRANSLATE 函式可以高效地取代多個字元，而 SQL Server 則需要結合 PATINDEX 或其他函式實作類別似功能。效能也是重要的考量因素，尤其在處理大量資料時，選擇效率更高的函式組合至關重要。此外，正規表示式提供更靈活的字串處理方式，但在某些資料函式庫系統中，其效能可能不如內建函式。

從字串中提取數字字元

在處理字串資料時，經常需要提取其中的數字部分。本文將介紹如何在不同的資料函式庫系統中實作這一功能，包括 PostgreSQL、SQL Server、DB2、Oracle 和 MySQL。

使用 TRANSLATE、REPLACE 和 STRPOS 函式

在 PostgreSQL 中，可以利用 TRANSLATE、REPLACE 和 STRPOS 函式來提取字串中的數字字元。

SELECT CAST(
    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 AS INTEGER) AS mixed
FROM V
WHERE STRPOS(TRANSLATE(mixed, '0123456789', '9999999999'), '9') > 0;

內容解密：

TRANSLATE(mixed, '0123456789', '9999999999') 將所有數字轉換為 ‘9’，便於統一處理。
REPLACE(TRANSLATE(mixed, '0123456789', '9999999999'), '9', '') 移除所有數字字元，得到非數字部分。
TRANSLATE(mixed, 非數字部分, RPAD('#', LENGTH(mixed), '#')) 將非數字字元轉換為 ‘#’。
REPLACE(..., '#', '') 移除 ‘#’，得到純數字字元。
STRPOS(TRANSLATE(mixed, '0123456789', '9999999999'), '9') > 0 過濾掉不包含數字的行。

SQL Server 中的實作

SQL Server 不支援 TRANSLATE 函式，但可以使用 ISNUMERIC 函式來判斷字串是否包含數字。

MySQL 中的實作

MySQL 可以透過逐一檢查字串中的每個字元，並使用 GROUP_CONCAT 函式來拼接數字字元。

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;

內容解密：

子查詢中，遍歷字串的每個字元，並檢查其 ASCII 碼是否在 ‘0’ 至 ‘9’ 之間。
使用 GROUP_CONCAT 將符合條件的字元（即數字）按原順序拼接起來。
將拼接結果轉換為無符號整數。

從字串中擷取第n個子字串的技術解析

在處理資料函式庫中的字串時，經常需要從一個由特定分隔符號（如逗號）分隔的字串中擷取特定的子字串。本文將詳細介紹如何在不同的資料函式倉管理系統（DBMS）中實作這一功能，並提供具體的SQL範例程式碼及其解析。

問題描述

給定一個檢視（view）V，其中包含一個名為NAME的欄位，該欄位的值是由逗號分隔的多個名字。我們的目標是從每個字串中提取第二個名字。

原始資料範例

select * from v

結果：

NAME
---
-
---
-
---
-
---
-
---
mo,larry,curly
tina,gina,jaunita,regina,leena

預期結果：

SUB
---
--
larry
gina

解決方案

DB2

DB2使用ROW_NUMBER()函式來為每個名字分配一個序號，並透過子查詢提取第二個名字。

select substr(c,2,locate(',',c,2)-2)
from (
  select pos, name, substr(name, pos) c,
         row_number() over(partition by name order by length(substr(name,pos)) desc) rn
  from (
    select ',' || csv.name || ',' as name,
           cast(iter.pos as integer) as pos
    from V csv,
         (select row_number() over() pos from t100) iter
    where iter.pos <= length(csv.name) + 2
  ) x
  where length(substr(name,pos)) > 1
  and substr(substr(name,pos),1,1) = ','
) y
where rn = 2

內容解密：

首先，將原始字串前後加上逗號，以統一處理邊界情況。
使用ROW_NUMBER()函式對每個名字進行編號。
透過子查詢篩選出第二個名字。

MySQL

MySQL使用SUBSTRING_INDEX函式來提取指定位置的子字串。

select name
from (
  select iter.pos,
         substring_index(substring_index(src.name,',',iter.pos),',',-1) name
  from V src,
       (select id pos from t10) iter
  where iter.pos <= length(src.name) - length(replace(src.name,',',''))
) x
where pos = 2

內容解密：

使用SUBSTRING_INDEX函式提取指定位置的子字串。
透過子查詢遍歷每個名字，並提取第二個名字。

Oracle

Oracle使用SUBSTR和INSTR函式來定位和提取子字串。

select sub
from (
  select iter.pos,
         src.name,
         substr(src.name, instr(src.name,',',1,iter.pos)+1,
                instr(src.name,',',1,iter.pos+1) - instr(src.name,',',1,iter.pos)-1) sub
  from (select ','||name||',' as name from V) src,
       (select rownum pos from emp) iter
  where iter.pos < length(src.name)-length(replace(src.name,','))
)
where pos = 2

內容解密：

在原始字串前後加上逗號，以方便處理。
使用INSTR函式定位逗號的位置，並使用SUBSTR提取第二個名字。

PostgreSQL

PostgreSQL使用SPLIT_PART函式來直接分割字串並提取指定部分。

select name
from (
  select iter.pos, split_part(src.name,',',iter.pos) as name
  from (select id as pos from t10) iter,
       (select cast(name as text) as name from v) src
  where iter.pos <= length(src.name)-length(replace(src.name,',',''))+1
) x
where pos = 2

內容解密：

使用SPLIT_PART函式直接根據逗號分割字串並提取第二個部分。

SQL Server

SQL Server使用STRING_SPLIT和STRING_AGG函式來實作相同的功能。

with agg_tab(name)
as (select STRING_AGG(name,',') from V)
select value from STRING_SPLIT((select name from agg_tab),',')
where [key] = 1  -- 注意：這裡假設[key]代表分割後的序號，實際上可能需要調整以符合具體需求

內容解密：

使用STRING_AGG將所有名字聚合成一個字串。
使用STRING_SPLIT分割該字串並提取需要的部分。

解析字串中的第n個子字串

在處理包含分隔符的字串時，經常需要提取其中的特定子字串。本文將介紹如何在不同資料函式倉管理系統（DBMS）中實作這一功能。

問題描述

假設有一個表 V，其中包含一個名為 name 的列，該列的值是由逗號分隔的字串。現在，需要提取每個字串中的第n個子字串。

解決方案

MySQL

MySQL 提供了 SUBSTRING_INDEX 函式，可以用來提取字串中的子字串。結合一個迭代器表，可以實作提取第n個子字串的功能。

SELECT iter.pos, src.name AS name1,
       SUBSTRING_INDEX(src.name, ',', iter.pos) AS name2,
       SUBSTRING_INDEX(
           SUBSTRING_INDEX(src.name, ',', iter.pos), ',', -1) AS name3
FROM (SELECT id AS pos FROM t10) iter,
     V src
WHERE iter.pos <= LENGTH(src.name) - LENGTH(REPLACE(src.name, ',', ''))

SQL Server

SQL Server 提供了 STRING_SPLIT 函式，可以用來分割字串。但是，該函式需要將字串轉換為單個值，因此需要使用 CTE 來實作。

WITH SplitCTE AS (
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS pos
    FROM STRING_SPLIT((SELECT name FROM V WHERE id = 1), ',')
)
SELECT pos, value
FROM SplitCTE
WHERE pos = 2;

Oracle

Oracle 可以使用遞迴查詢和 SUBSTR、INSTR 函式來實作提取第n個子字串的功能。

SELECT iter.pos, src.name,
       SUBSTR(src.name,
              INSTR(src.name, ',', 1, iter.pos) + 1,
              INSTR(src.name, ',', 1, iter.pos + 1) -
              INSTR(src.name, ',', 1, iter.pos) - 1) AS sub
FROM (SELECT ',' || name || ',' AS name FROM V) src,
     (SELECT ROWNUM AS pos FROM emp) iter
WHERE iter.pos < LENGTH(src.name) - LENGTH(REPLACE(src.name, ','))

PostgreSQL

PostgreSQL 可以使用 SPLIT_PART 函式來提取字串中的子字串。

SELECT iter.pos, src.name AS name1,
       SPLIT_PART(src.name, ',', iter.pos) AS name2
FROM (SELECT id AS pos FROM t10) iter,
     (SELECT CAST(name AS text) AS name FROM V) src
WHERE iter.pos <= LENGTH(src.name) - LENGTH(REPLACE(src.name, ',', '')) + 1

程式碼解密：

MySQL 程式碼解析

迭代器表：使用 (SELECT id AS pos FROM t10) 建立了一個迭代器表，用於生成從1到n的數字序列。
SUBSTRING_INDEX 函式：用於提取字串中的子字串。第一次呼叫提取了從開始到第n個逗號的子字串，第二次呼叫提取了最後一個逗號後面的子字串，即第n個子字串。
WHERE 子句：用於過濾掉那些 pos 大於字串中逗號數量 + 1 的行。

SQL Server 程式碼解析

CTE（公共表表達式）：用於將 STRING_SPLIT 分割的字串賦予行號。
STRING_SPLIT 函式：用於將字串分割成多行。
ROW_NUMBER() 函式：用於為分割後的每一行賦予一個連續的行號。

Oracle 程式碼解析

遞迴查詢：使用遞迴查詢來生成從1到n的數字序列。
SUBSTR 和 INSTR 函式：用於提取字串中的子字串。INSTR 用於找到第n個逗號的位置，SUBSTR 用於提取該位置後面的子字串。
WHERE 子句：用於過濾掉那些 pos 大於字串中逗號數量 + 1 的行。

PostgreSQL 程式碼解析

SPLIT_PART 函式：用於提取字串中的第n個子字串。
迭代器表：使用 (SELECT id AS pos FROM t10) 建立了一個迭代器表，用於生成從1到n的數字序列。
WHERE 子句：用於過濾掉那些 pos 大於字串中逗號數量 + 1 的行。

解析IP地址的不同資料函式庫實作方法

在處理網路相關的資料時，經常需要對IP地址進行解析。不同的資料函式庫系統提供了不同的函式來實作這一功能。本文將介紹如何在MySQL、Oracle、PostgreSQL和SQL Server中解析IP地址。

MySQL中的實作

MySQL提供了SUBSTRING_INDEX函式，可以輕鬆地解析IP地址。

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip, '.', 1), '.', -1) AS a,
  SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip, '.', 2), '.', -1) AS b,
  SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip, '.', 3), '.', -1) AS c,
  SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip, '.', 4), '.', -1) AS d
FROM (SELECT '92.111.0.2' AS ip FROM t1) y;

內容解密：

SUBSTRING_INDEX(string, delimiter, count)函式用於傳回string中在delimiter出現count次之前的子字串。
當count為負數時，函式傳回最後count個delimiter之後的子字串。
這裡透過巢狀使用SUBSTRING_INDEX，先取得指定位置的段落，再提取出該段落的值。

Oracle中的實作

Oracle使用內建的SUBSTR和INSTR函式來解析IP地址。

SELECT 
  ip,
  SUBSTR(ip, 1, INSTR(ip, '.') - 1) AS a,
  SUBSTR(ip, INSTR(ip, '.') + 1, INSTR(ip, '.', 1, 2) - INSTR(ip, '.') - 1) AS b,
  SUBSTR(ip, INSTR(ip, '.', 1, 2) + 1, INSTR(ip, '.', 1, 3) - INSTR(ip, '.', 1, 2) - 1) AS c,
  SUBSTR(ip, INSTR(ip, '.', 1, 3) + 1) AS d
FROM (SELECT '92.111.0.2' AS ip FROM t1);

內容解密：

INSTR(string, substring)函式傳回substring在string中第一次出現的位置。
SUBSTR(string, start_position, length)函式傳回string從start_position開始，長度為length的子字串。
這裡透過多次使用INSTR來定位.的位置，然後使用SUBSTR提取各個段落。

PostgreSQL中的實作

PostgreSQL提供了SPLIT_PART函式，可以直接用來解析IP地址。

SELECT 
  SPLIT_PART(y.ip, '.', 1) AS a,
  SPLIT_PART(y.ip, '.', 2) AS b,
  SPLIT_PART(y.ip, '.', 3) AS c,
  SPLIT_PART(y.ip, '.', 4) AS d
FROM (SELECT CAST('92.111.0.2' AS TEXT) AS ip FROM t1) AS y;

內容解密：

SPLIT_PART(string, delimiter, field)函式根據delimiter將string分割，並傳回第field個部分。
這裡直接使用SPLIT_PART函式，透過指定.作為分隔符，來提取IP地址的各個部分。

SQL Server中的實作

SQL Server使用遞迴的CTE（Common Table Expression）來模擬迭代過程，同時利用字串函式來解析IP地址。

WITH x (pos, ip) AS (
  SELECT 1 AS pos, '.92.111.0.222' AS ip FROM t1
  UNION ALL
  SELECT pos + 1, ip FROM x WHERE pos + 1 <= 20
)
SELECT 
  MAX(CASE WHEN rn = 1 THEN e END) AS a,
  MAX(CASE WHEN rn = 2 THEN e END) AS b,
  MAX(CASE WHEN rn = 3 THEN e END) AS c,
  MAX(CASE WHEN rn = 4 THEN e END) AS d
FROM (
  SELECT pos, c, d,
    CASE WHEN CHARINDEX('.', d) > 0 THEN SUBSTRING(d, 1, CHARINDEX('.', d) - 1)
         ELSE d END AS e,
    ROW_NUMBER() OVER (ORDER BY pos DESC) AS rn
  FROM (
    SELECT pos, ip, RIGHT(ip, pos) AS c, SUBSTRING(RIGHT(ip, pos), 2, LEN(ip)) AS d
    FROM x
    WHERE pos <= LEN(ip) AND SUBSTRING(RIGHT(ip, pos), 1, 1) = '.'
  ) x
) y;

圖表示例：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫字串數字擷取技術解析

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

圖表翻譯： 此圖表示SQL Server中解析IP地址的流程。首先開始解析IP，接著使用CTE進行遞迴操作，定位IP地址中的’.‘位置，然後提取各個段落的數字，最終傳回解析結果。

內容解密：

使用遞迴CTE來迭代處理IP地址。
CHARINDEX(substring, string)函式傳回substring在string中的位置。
SUBSTRING(string, start, length)函式用於提取子字串。
ROW_NUMBER()函式為結果集中的每一行分配一個唯一的序號。
這裡透過遞迴和字串函式的結合使用，來解析IP地址。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。