SUBSTRING_INDEX() 函數

SUBSTRING_INDEX(str, delim, count)

• str：來源字串
• delim：分隔符 (可為多字元)
• count：
  • 正數：從左往右，返回第 count 個分隔符**左側**內容
  • 負數：從右往左，返回第 |count| 個分隔符**右側**內容
  • 0：返回空字串

經典範例

SELECT SUBSTRING_INDEX('www.111cn.net', '.', 2);
-- 'www.111cn'  (第 2 個 '.' 左側)

SELECT SUBSTRING_INDEX('www.111cn.net', '.', -2);
-- '111cn.net'  (倒數第 2 個 '.' 右側)

SELECT SUBSTRING_INDEX('a,b,c,d', ',', 3);
-- 'a,b,c'

SELECT SUBSTRING_INDEX('a,b,c,d', ',', -3);
-- 'b,c,d'

實戰：從標題提取數字排序

場景：文章標題如 「第 10 章 介紹」、「Chapter 5 詳解」，需提取數字排序。

-- 假設標題格式固定含數字
SELECT title,
       CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title, '第', -1), '章', 1) AS UNSIGNED) AS chap_num
FROM articles
ORDER BY chap_num;

字符串轉數字技巧

最簡單：+0 或 CAST

SELECT '123' + 0;        -- 123 (數值)
SELECT CAST('123' AS UNSIGNED);  -- 123
SELECT 'abc' + 0;        -- 0 (非數字開頭變 0)
SELECT '123abc' + 0;     -- 123 (截取前導數字)

配合 SUBSTRING_INDEX 完整範例

-- 提取標題首個數字作為排序依據
SELECT title,
       (SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', 2), ' ', -1) + 0) AS sort_num
FROM articles
WHERE title REGEXP '[0-9]'
ORDER BY sort_num;

補充：SUBSTRING() 系列函數

語法	說明	範例結果
SUBSTRING(str, pos)	從 pos 位置到結尾 (1-based)	SUBSTRING('Quadratically',5) → 'ratically'
SUBSTRING(str, pos, len)	從 pos 取 len 字元	SUBSTRING('Quadratically',5,6) → 'ratica'
SUBSTRING(str, -pos)	從右往左第 pos 字元到結尾	SUBSTRING('Sakila', -3) → 'ila'
SUBSTRING(str, -pos, len)	從右往左第 pos 字元取 len	SUBSTRING('Sakila', -5, 3) → 'aki'
SUBSTRING(str FROM pos FOR len)	SQL 標準語法	同上

效能提示

• 字符串函數在 WHERE/JOIN/ORDER BY 中使用會**導致索引失效**
• 大量資料排序/過濾建議：冗餘欄位存儲提取值、虛擬欄位 (Generated Column)、應用層處理
• MySQL 8.0 支援函數索引：INDEX ((CAST(SUBSTRING_INDEX(title, ' ', -1) AS UNSIGNED)))