SQL字符串处理大全：从基础到高阶的完整指南

Delores，2025年4月9日

字符串处理是SQL中最常见的操作之一，广泛应用于数据清洗、格式化、分析和报告生成。本文系统整理了SQL中的字符串函数，涵盖**基础操作、高级处理、正则表达式和跨数据库差异**，并提供实际场景示例，帮助开发者快速掌握字符串处理的核心技能。

---

### 一、基础字符串操作函数

#### 1. **拼接与分割**
| 函数 | 语法示例 | 功能说明 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `CONCAT` | `CONCAT(str1, str2, ...)` | 拼接多个字符串，若任一参数为NULL，某些数据库（如MySQL）返回NULL，某些忽略NULL。 |
| `CONCAT_WS` | `CONCAT_WS(分隔符, str1, str2)` | 用指定分隔符拼接字符串，自动跳过NULL（如 `CONCAT_WS('-', 'A', NULL, 'B') → 'A-B'`）。 |
| `SPLIT_PART` | `SPLIT_PART('A,B,C', ',', 2)` | 按分隔符分割字符串并取第N部分（如返回'B'），PostgreSQL支持。 |

#### 2. **截取与定位**
| 函数 | 语法示例 | 说明 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `SUBSTR`/`SUBSTRING` | `SUBSTR('Hello', 2, 3)` | 从第2字符开始截取3个字符（返回'ell'），注意**起始索引从1开始**。 |
| `LEFT`/`RIGHT` | `LEFT('SQL', 2)` | 取左/右侧指定长度字符（返回'SQ'）。 |
| `INSTR`/`CHARINDEX` | `INSTR('Hello', 'l')` | 返回子字符串首次出现的位置（返回3，注意MySQL从1开始计数）。 |

#### 3. **长度与格式化**
| 函数 | 语法示例 | 说明 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `LENGTH`/`LEN` | `LENGTH('中文')` | 返回字节数（UTF-8中'中文'返回6），`CHAR_LENGTH`返回字符数（返回2）。 |
| `UPPER`/`LOWER` | `UPPER('abc')` | 转换字符串为大写/小写（返回'ABC'）。 |
| `TRIM`/`LTRIM`/`RTRIM` | `TRIM(' SQL ')` | 删除两端空格（返回'SQL'），支持指定删除字符（如`TRIM(LEADING '0' FROM '000123')`）。 |

---

### 二、高级字符串处理

#### 1. **字符串替换与填充**
| 函数 | 语法示例 | 场景案例 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `REPLACE` | `REPLACE('A,B,C', ',', '-')` | 替换所有匹配的子字符串（返回'A-B-C'）。 |
| `TRANSLATE` | `TRANSLATE('abc', 'ab', 'XY')` | 按字符映射替换（'a→X', 'b→Y'，返回'XYc'），Oracle/PostgreSQL支持。 |
| `LPAD`/`RPAD` | `LPAD('5', 3, '0')` | 左侧填充字符到指定长度（返回'005'）。 |

#### 2. **正则表达式**
| 函数 | 语法示例 | 说明 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `REGEXP_REPLACE` | `REGEXP_REPLACE('A1B2', '[0-9]', 'X')` | 正则替换（返回'AXBX'），支持复杂模式匹配。 |
| `REGEXP_SUBSTR` | `REGEXP_SUBSTR('ID:123', '[0-9]+')` | 提取匹配正则的子字符串（返回'123'）。 |
| `REGEXP_LIKE` | `WHERE REGEXP_LIKE(name, '^J')` | 过滤符合正则的行（如筛选以J开头的名字）。 |

#### 3. **聚合与生成**
| 函数 | 语法示例 | 场景案例 |
|----------------------|----------------------------------|--------------------------------------------------------------------------|
| `GROUP_CONCAT` | `GROUP_CONCAT(name SEPARATOR ',')` | 将分组结果拼接为字符串（MySQL特有，返回'A,B,C'）。 |
| `STRING_AGG` | `STRING_AGG(name, ',' ORDER BY id)` | 功能同上，PostgreSQL/SQL Server支持（需指定排序）。 |
| `REPEAT` | `REPEAT('X', 3)` | 重复字符串多次（返回'XXX'）。 |

---

### 三、跨数据库差异与避坑指南

#### 1. **函数名与参数差异**
• **截取函数**：
• MySQL：`SUBSTR(str, start, length)`
• SQL Server：`SUBSTRING(str, start, length)`
• PostgreSQL：`SUBSTRING(str FROM start FOR length)`
• **正则表达式**：
• PostgreSQL支持`~`操作符（如`WHERE str ~ '^A'`），MySQL需用`REGEXP`。

#### 2. **NULL处理逻辑**
• `CONCAT('A', NULL)`：
• MySQL返回'A'（忽略NULL），Oracle/SQL Server返回NULL。
• 使用`COALESCE`规避NULL：
```sql
SELECT CONCAT(COALESCE(str1, ''), COALESCE(str2, '')) FROM table;
```

#### 3. **编码与字符集问题**
• 多字节字符（如中文）处理：
• `LENGTH('中文')`在UTF-8中返回6（字节数），`CHAR_LENGTH`返回2（字符数）。
• 使用`CAST`转换字符集：`CAST(str AS VARCHAR(10) CHARACTER SET utf8)`。

---

### 四、实战场景案例

#### 1. **数据清洗：手机号脱敏**
```sql
-- 将'13812345678'处理为'138****5678'
SELECT CONCAT(SUBSTR(phone, 1, 3), '****', SUBSTR(phone, 8))
FROM users;
```

#### 2. **动态SQL生成**
```sql
-- 拼接查询条件（如筛选状态为1或3的用户）
SELECT CONCAT('SELECT * FROM orders WHERE status IN (', GROUP_CONCAT(status), ')')
FROM (SELECT DISTINCT status FROM config) AS t;
```

#### 3. **地址格式化**
```sql
-- 将'北京市;海淀区;中关村'转换为'北京市-海淀区-中关村'
SELECT REPLACE(address, ';', '-') AS formatted_address
FROM addresses;
```

---

### 五、总结
掌握SQL字符串函数是高效处理数据的基础，需注意：
1. **明确需求**：选择截取、替换、拼接或正则匹配。
2. **关注跨数据库差异**：如函数名、参数顺序和NULL处理逻辑。
3. **性能优化**：避免在大数据集上频繁使用正则或复杂嵌套函数。
4. **测试验证**：尤其在处理多语言和特殊字符时，确保结果符合预期。