萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL SUBSTR/SUBSTRING 函數完全指南:字串截取、負數索引與跨資料庫差異

SUBSTR (Oracle/MySQL/PostgreSQL) / SUBSTRING (SQL Server/Standard SQL) 是字串截取的核心函數。本文詳細解析參數意義、正負索引行為、與 Java substring 的差異,並提供跨資料庫對照表。

標準語法

-- Oracle / MySQL / PostgreSQL / DB2
SUBSTR(string, start_position, [length])

-- SQL Server / ANSI SQL
SUBSTRING(string, start, length)

參數詳細說明

參數說明特殊規則
string來源字串 (欄位/變數/常數)
start起始位置,**1-based** (第一個字元位置為 1)正數:從左往右數
負數:從右往左數 (Oracle/MySQL/PostgreSQL 支援)
0:視同 1 (Oracle/標準 SQL)
length截取長度 (選填)省略 = 截取至字串結尾;
負數/零 → 回傳空字串 (視資料庫而定)

範例解析

-- 基礎截取
SELECT SUBSTR('abcdefg', 3, 4);   -- 'cdef' (從第 3 字元取 4 個)
SELECT SUBSTRING('abcdefg', 3, 4); -- 'cdef' (SQL Server)

-- 負數起始位置 (Oracle/MySQL/PostgreSQL)
SELECT SUBSTR('abcdefg', -3, 4);  -- 'efg' (從倒數第 3 字元 'e' 開始取 4 個,實際只取到結尾)
-- SQL Server SUBSTRING **不支援負數起始**,需自行計算:SUBSTRING(str, LEN(str)+start, length)

-- 起始為 0
SELECT SUBSTR('abcdefg', 0, 3);   -- 'abc' (Oracle: 0 視同 1)
SELECT SUBSTRING('abcdefg', 0, 3); -- 'ab' (SQL Server: 0 為第 0 位,實際從 1 開始取 3 個 → 'abc'? 實測 SQL Server start=0 視同 1)
-- 實測 SQL Server 2019: SUBSTRING('abcdefg',0,3) → 'abc' (同 start=1)

跨資料庫行為差異總表

資料庫函數名支援負數 startstart=0 行為length 省略超出長度處理
OracleSUBSTR✅ 從右往左視同 1至結尾回傳可用部分,不報錯
MySQLSUBSTR / SUBSTRING✅ 從右往左視同 1至結尾回傳可用部分
PostgreSQLSUBSTRING / SUBSTR✅ 從右往左視同 1至結尾回傳可用部分
SQL ServerSUBSTRING❌ (需自算)視同 1**必填** (不可省略)回傳可用部分
DB2SUBSTR✅視同 1至結尾回傳可用部分
AccessMid❌錯誤必填錯誤

SQL Server 實現「從右往左截取」的標準寫法

-- 取右邊 3 碼
SELECT RIGHT('abcdefg', 3);                    -- 'efg' (最簡單)
SELECT SUBSTRING('abcdefg', LEN('abcdefg') - 3 + 1, 3); -- 通用寫法

-- 取倒數第 3 碼開始的 4 碼 (對應 Oracle SUBSTR(str, -3, 4))
DECLARE @str VARCHAR(20) = 'abcdefg';
SELECT SUBSTRING(@str, LEN(@str) - 3 + 1, 4);  -- 'efg'

與 Java String.substring() 差異對照

特性SQL SUBSTR/SUBSTRINGJava substring
索引基準**1-based** (第一字元=1)**0-based** (第一字元=0)
參數模式(start, length)(beginIndex, endIndex) **結束索引不包含**
負數索引部份支援 (Oracle/MySQL/PG)不支援 (拋出例外)
範例SUBSTR('abc',1,2)='ab'"abc".substring(0,2)='ab'
結束邊界長度超出截至結尾endIndex > length 拋出 StringIndexOutOfBoundsException

實務常用模式

-- 1. 固定寬度欄位解析 (如身分證、電話)
SELECT SUBSTRING(Phone, 1, 3) AS AreaCode, SUBSTRING(Phone, 4, 8) AS Number FROM Customers;

-- 2. 移除前後綴
SELECT SUBSTRING(Col, 2, LEN(Col)-2) FROM Table;  -- 去除首尾各 1 字元

-- 3. 截取最後 N 碼
SELECT RIGHT(Col, 4) AS Last4;  -- SQL Server/MySQL/Oracle 皆支援 RIGHT()

-- 4. 動態截取 (搭配 CHARINDEX/PATINDEX)
SELECT SUBSTRING(Url, CHARINDEX('://', Url)+3, LEN(Url)) FROM Logs;  -- 移除協定頭

效能提示

  • SUBSTRING 在 WHERE 條件中使用會導致**索引無法尋找** (SARGable 失效)。
  • 改寫為 WHERE Col LIKE 'prefix%' 或使用**計算欄位 + 索引**:
    ALTER TABLE T ADD Prefix3 AS SUBSTRING(Col,1,3) PERSISTED; CREATE INDEX IX_Prefix3 ON T(Prefix3);
  • 大量字串操作考慮 CLR 函數或應用層處理。

熟練掌握字串截取函數的跨平台差異,能讓您在異構資料庫環境、ETL 轉換、遷移專案中游刃有餘。

copyright © 萬盛學電腦網 all rights reserved