萬盛學電腦網

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

SQL SUBSTR/SUBSTRING 函數完全指南:語法差異、負數索引與跨資料庫對照

SUBSTR/SUBSTRING 是 SQL 中最常用的字串截取函數,但不同資料庫對參數定義、索引起始位、負數語義有細微差異。本文以標準 SQL 為基準,對比 MySQL、Oracle、PostgreSQL、SQL Server、Java substring 的行為差異,提供通用寫法與避坑清單。

標準語法

SUBSTR(string, start[, length])
SUBSTRING(string FROM start [FOR length])  -- SQL 標準寫法

參數定義

  • string:來源字串
  • start:起始位置
    • 正數:從左往右,1 為第一個字元(SQL 標準、MySQL、Oracle、PostgreSQL)
    • 負數:從右往左,-1 為最後一個字元(MySQL、Oracle、PostgreSQL)
    • SQL Server:start 從 1 開始,不支援負數
  • length:截取長度(可選),省略則截取到字串末尾

跨資料庫行為對照表

語句MySQLOraclePostgreSQLSQL ServerJava substring
SUBSTR('abcdefg', 3, 4)cdefcdefcdefcdefN/A
SUBSTR('abcdefg', -3, 4)efgefgefg錯誤/NULLN/A
SUBSTR('abcdefg', 0, 3)abc (視同 1)abc (視同 1)abc (視同 1)空字串N/A
SUBSTRING('abcdefg', 1, 3)abcabcabcabcN/A
'abcdefg'.substring(2)N/AN/AN/AN/Acdefg (0-based)
'hamburger'.substring(3,8)N/AN/AN/AN/Aburge (endIndex 不包含)

關鍵差異總結

  1. 索引基準:SQL 標準 1-based;Java 0-based
  2. 負數支援:MySQL/Oracle/PG 支援;SQL Server 不支援(需用 RIGHT() 或 REVERSE() 組合)
  3. start=0:MySQL/Oracle/PG 視同 1;SQL Server 回傳空字串
  4. length 超出:所有資料庫均回傳至字串末尾,不報錯
  5. 多位元組字元:MySQL utf8mb4 下按字元計數;Oracle AL32UTF8 同;SQL Server NVARCHAR 按字元,VARCHAR 按位元組

通用相容寫法

取最後 N 字元

-- MySQL / Oracle / PostgreSQL
SUBSTR(col, -N)

-- SQL Server
RIGHT(col, N)
-- 或
SUBSTRING(col, LEN(col) - N + 1, N)

去除首尾字元

-- 通用:去首尾各 1 字元
SUBSTR(col, 2, LENGTH(col) - 2)
-- MySQL 專用
TRIM(BOTH 'x' FROM col)

效能提醒

  • SUBSTR(col, 1, 10) = 'prefix' 可用索引(MySQL 8.0+ 函數索引、Oracle FBI、PG Expression Index)
  • SUBSTR(col, -5) = 'suffix' 通常導致全表掃描,考慮反轉欄位或全文索引
  • 大量字串操作在應用層完成(Java/Go/Python)通常比資料庫層更靈活高效

實用範例集

-- 取域名
SUBSTR_INDEX(email, '@', -1)           -- MySQL 專用
SUBSTR(email, INSTR(email, '@') + 1)   -- 通用

-- 取副檔名
SUBSTR(filename, INSTR(filename, '.') + 1)

-- 固定寬度欄位解析 (定長匯入)
SUBSTR(raw_line, 1, 10)   AS col1,
SUBSTR(raw_line, 11, 8)   AS col2,
SUBSTR(raw_line, 19, 20)  AS col3

掌握 SUBSTR 的跨平台差異,能讓你在異構資料庫遷移、ETL 腳本編寫、跨平台 SQL 重構中避開隱形坑。建議團隊建立「SQL 方言差異檢查清單」,Code Review 時逐項核對。

copyright © 萬盛學電腦網 all rights reserved