十大核心函數速查表
| 函數 | 語法 | 返回值 | 關鍵行為/陷阱 |
|---|
| LEN | `LEN(str)` | INT | **不計尾隨空格**；`LEN('abc ')` = 3。NULL→NULL |
| DATALENGTH | `DATALENGTH(expr)` | INT | **字節長度**；含尾隨空格。`N'abc'` = 6 字節 |
| LOWER/UPPER | `LOWER/UPPER(str)` | VARCHAR/NVARCHAR | 大小寫轉換。NULL→NULL |
| LTRIM/RTRIM | `LTRIM/RTRIM(str)` | VARCHAR/NVARCHAR | **僅修剪首/尾空格**；中間空格不動。NULL→NULL |
| TRIM | `TRIM(str)` | VARCHAR/NVARCHAR | SQL 2017+ 兩端修剪。`LTRIM(RTRIM())` 兼容寫法 |
| SUBSTRING | `SUBSTRING(str, start, len)` | VARCHAR/NVARCHAR | **start 從 1 開始**；0/負數視為邏輯前移。NULL→NULL |
| CHARINDEX | `CHARINDEX(sub, str [, start])` | INT | 返回首次出現位置 (1-based)；找不到→0。NULL→NULL |
| LEFT/RIGHT | `LEFT/RIGHT(str, len)` | VARCHAR/NVARCHAR | 等價 `SUBSTRING(str,1,len)` / `SUBSTRING(str, LEN-len+1, len)`。NULL→NULL |
| ASCII | `ASCII(str)` | INT | 返回首字符 ASCII 碼。NULL→NULL |
| UNICODE | `UNICODE(nstr)` | INT | 返回首字符 Unicode 碼點。NULL→NULL |
高頻陷阱與避坑指南
- LEN 尾隨空格不計 → 需精確長度用 `DATALENGTH` / `LEN(RTRIM(str))`
2. SUBSTRING start=0 → 視為 start=1 但長度扣減 1，極易出錯，**永遠用 ≥1**
3. CHARINDEX 不區分大小寫** (取決於排序規則) → 需區分大小寫用 `COLLATE Latin1_General_CS_AS`
4. 隱式轉換性能殺手 → `WHERE VARCHARCol = N'Unicode'` 導致掃描，顯式 `CAST`/參數類型匹配
5. 大字段操作 → `VARCHAR(MAX)` 用 `.WRITE()` 部分更新，避免整值重寫
實戰組合模式
-- 1. 截取首個單詞
LEFT(str, CHARINDEX(' ', str + ' ') - 1)
-- 2. 反轉字符串 (遞歸 CTE / 數字表)
-- 3. 分隔符分裂行 (STRING_SPLIT SQL 2016+ / XML / 數字表)
SELECT value FROM STRING_SPLIT('a,b,c', ',');
-- 4. 掩碼敏感信息 (手機/身份證)
STUFF(Phone, 4, 4, '****') -- 138****5678
-- 5. 動態 SQL 安全拼接
QUOTENAME(@TableName) + '.' + QUOTENAME(@ColName)
現代替代函數 (SQL 2017+)
| 舊寫法 | 新函數 | 優勢 |
|---|
| `LTRIM(RTRIM(str))` | `TRIM(str)` | 簡潔、標準 |
| `STUFF/REPLACE` 連接 | `STRING_AGG` / `CONCAT_WS` | 聚合/分隔符原生支援 |
| 多重 `REPLACE` | `TRANSLATE` | 字符級映射、單次掃描 |
| `SUBSTRING + CHARINDEX` 解析 | `PARSE` / `JSON_VALUE` | 結構化解析 |
