SQL字符串处理函数实战指南：45个常用函数详解与数据清洗、文本分析应用

在日常的数据库操作与数据分析工作中，字符串处理是不可或缺的核心环节。无论是清洗繁杂的文本数据、提取关键信息字段，还是格式化最终的输出结果，都离不开SQL中功能丰富的字符串处理函数。这些函数覆盖了拼接、截取、替换、查找等多种应用场景，但不同数据库/中间件（如 MySQL、Oracle、Hive）在语法细节和功能支持上存在差异，容易造成混淆。本文将详细梳理 45 个最常用的 SQL 字符串处理函数，从基础的大小写转换到复杂的正则匹配与JSON解析，逐一介绍其语法、作用并提供应用示例。

一、字符串拼接函数

1. CONCAT()：将多个字符串顺序拼接为一个新字符串。若任一参数为 NULL，则返回 NULL。

   • 语法：CONCAT(str1, str2, ...)
   • 示例：CONCAT('SQL', ' ', '字符串') → 'SQL 字符串'

2. CONCAT_WS()：使用指定的分隔符（separator）将多个字符串拼接起来，并自动忽略 NULL 值。

   • 语法：CONCAT_WS(separator, str1, str2, ...)
   • 示例：CONCAT_WS('-', '2023', '10', NULL, '05') → '2023-10-05'

3. || 运算符：部分数据库（如 Oracle、PostgreSQL）支持的字符串连接运算符。

   • 语法：str1 || str2 || ...
   • 示例：'Hello' || ' ' || 'World' → 'Hello World'

二、字符串长度与测量函数

4. LENGTH() / LEN()：返回字符串的字节数（LENGTH）或字符数（LEN，SQL Server 常用）。

   • 语法：LENGTH(str) / LEN(str)
   • 示例：LENGTH('中国') → 6（UTF-8编码）；LEN('abc') → 3

5. CHAR_LENGTH() / CHARACTER_LENGTH()：返回字符串的字符数，与字符编码无关。

   • 语法：CHAR_LENGTH(str)
   • 示例：CHAR_LENGTH('中国abc') → 5

6. BIT_LENGTH()：返回字符串所占用的比特（bit）数。

   • 语法：BIT_LENGTH(str)
   • 示例：BIT_LENGTH('a') → 8（一个ASCII字符占1字节，即8比特）

三、字符串截取与提取函数

7. SUBSTRING() / SUBSTR()：从指定起始位置（start）截取指定长度（length）的子串。start为正从左起，为负从右起。

   • 语法：SUBSTRING(str, start, length) / SUBSTR(str, start, length)
   • 示例：SUBSTRING('SQL字符串', 2, 3) → 'QL字'；SUBSTR('数据库', -2) → '据库'

8. LEFT()：返回字符串左侧开始指定长度的子串。

   • 语法：LEFT(str, length)
   • 示例：LEFT('数据分析', 2) → '数据'

9. RIGHT()：返回字符串右侧开始指定长度的子串。

   • 语法：RIGHT(str, length)
   • 示例：RIGHT('Python', 3) → 'hon'

10. SUBSTRING_INDEX()：按照指定的分隔符（delimiter）截取子串。count为正则取左侧第count个分隔符之前的部分，为负则取右侧。

    • 语法：SUBSTRING_INDEX(str, delimiter, count)
    • 示例：SUBSTRING_INDEX('a.b.c.d', '.', 2) → 'a.b'；SUBSTRING_INDEX('a.b.c.d', '.', -2) → 'c.d'

四、字符串替换与修改函数

11. REPLACE()：将字符串中所有出现的old_str替换为new_str。

    • 语法：REPLACE(str, old_str, new_str)
    • 示例：REPLACE('abc123abc', 'abc', 'xyz') → 'xyz123xyz'

12. INSERT()：从字符串的start位置开始，删除length个字符，并插入new_str。

    • 语法：INSERT(str, start, length, new_str)
    • 示例：INSERT('Hello World', 7, 5, 'SQL') → 'Hello SQL'

13. TRANSLATE()：按照字符映射进行替换。from_str和to_str中的字符一一对应。

    • 语法：TRANSLATE(str, from_str, to_str)
    • 示例：TRANSLATE('123-abc', '1a', 'Xx') → 'X23-xbc'

14. REGEXP_REPLACE()：使用replace_str替换字符串中所有匹配正则表达式（regex）的部分。

    • 语法：REGEXP_REPLACE(str, regex, replace_str)
    • 示例：REGEXP_REPLACE('a1b2c3', '[0-9]', '*') → 'a*b*c*'

五、大小写转换函数

15. UPPER() / UCASE()：将字符串中的所有字母转换为大写。

    • 语法：UPPER(str) / UCASE(str)
    • 示例：UPPER('Hello') → 'HELLO'

16. LOWER() / LCASE()：将字符串中的所有字母转换为小写。

    • 语法：LOWER(str) / LCASE(str)
    • 示例：LOWER('WORLD') → 'world'

17. INITCAP()：将字符串中每个单词的首字母转为大写，其余字母转为小写（Oracle、PostgreSQL 支持）。

    • 语法：INITCAP(str)
    • 示例：INITCAP('hello world sql') → 'Hello World Sql'

六、空格处理函数

18. LTRIM()：去除字符串左侧（开头）的所有空格。

    • 语法：LTRIM(str)
    • 示例：LTRIM('  abc  ') → 'abc  '

19. RTRIM()：去除字符串右侧（末尾）的所有空格。

    • 语法：RTRIM(str)
    • 示例：RTRIM('  abc  ') → '  abc'

20. TRIM()：去除字符串两侧（或指定位置）的指定字符，默认去除空格。

    • 语法：TRIM([BOTH | LEADING | TRAILING] trim_str FROM str)
    • 示例：TRIM('x' FROM 'xxabcxx') → 'abc'；TRIM(LEADING ' ' FROM '  test') → 'test'

21. SPACE()：生成一个由指定数量空格组成的字符串。

    • 语法：SPACE(n)
    • 示例：CONCAT('a', SPACE(2), 'b') → 'a  b'

七、字符串查找与定位函数

22. LOCATE() / POSITION()：返回子串substr在字符串str中首次出现的位置（从1开始计数），未找到则返回0。

    • 语法：LOCATE(substr, str [, start]) / POSITION(substr IN str)
    • 示例：LOCATE('ab', 'aababc') → 2；POSITION('cd' IN 'abc') → 0

23. INSTR()：功能同 LOCATE，但参数顺序通常相反（Oracle 等数据库）。

    • 语法：INSTR(str, substr)
    • 示例：INSTR('abcde', 'cd') → 3

24. FIELD()：返回str在后续字符串列表（str1, str2, ...）中的索引位置，未找到则返回0。

    • 语法：FIELD(str, str1, str2, ...)
    • 示例：FIELD('b', 'a', 'b', 'c') → 2

25. FIND_IN_SET()：返回str在以逗号分隔的字符串列表str_list中的位置。

    • 语法：FIND_IN_SET(str, str_list)
    • 示例：FIND_IN_SET('mysql', 'hive,mysql,spark') → 2

八、字符串拆分与合并函数

26. SPLIT_STR()：按分隔符拆分字符串，返回第pos个元素（MySQL 自定义函数）。

    • 语法：SPLIT_STR(str, delimiter, pos)
    • 示例：SPLIT_STR('a,b,c,d', ',', 3) → 'c'

27. STRING_AGG()：将多行查询结果中的某个字符串表达式，按指定的分隔符合并成一行（SQL Server, PostgreSQL 等）。

    • 语法：STRING_AGG(expr, separator)
    • 示例：对包含 ('a'),('b'),('c') 的列执行 STRING_AGG(col, ';') → 'a;b;c'

28. REGEXP_SPLIT_TO_TABLE()：按正则表达式将字符串拆分为多行结果集（PostgreSQL 支持）。

    • 语法：REGEXP_SPLIT_TO_TABLE(str, regex)
    • 示例：REGEXP_SPLIT_TO_TABLE('a,b;c', '[;,]') 返回三行：a, b, c

29. SPLIT()：按分隔符将字符串拆分为数组（Hive, PostgreSQL 等支持）。

    • 语法：SPLIT(str, separator)
    • 示例：SPLIT('a,b,c,d', ',') → ['a', 'b', 'c', 'd']

30. split_part()：按分隔符拆分字符串后，返回指定位置（position，从1开始）的子串（Hive 等支持）。

    • 语法：split_part(str, delimiter, position)
    • 示例：split_part('a,b,c,d', ',', 3) → 'c'

九、正则表达式函数

31. REGEXP_LIKE()：判断字符串是否匹配给定的正则表达式。

    • 语法：REGEXP_LIKE(str, regex)
    • 示例：REGEXP_LIKE('123abc', '^[0-9]+$') → 0（不匹配纯数字）

32. REGEXP_SUBSTR()：返回字符串中匹配正则表达式的第一个子串。

    • 语法：REGEXP_SUBSTR(str, regex)
    • 示例：REGEXP_SUBSTR('abc123def', '[0-9]+') → '123'

33. regexp_extract()：通过正则表达式提取指定捕获分组（group_index）的内容（Hive 支持）。

    • 语法：regexp_extract(str, regex, group_index)
    • 示例：regexp_extract('user_123_name', 'user_(\\d+)_name', 1) → '123'

十、字符编码与转换函数

34. ASCII()：返回字符串第一个字符的 ASCII 码值（仅对单字节字符有效）。

    • 语法：ASCII(str)
    • 示例：ASCII('A') → 65

35. CHAR()：将给定的一个或多个 ASCII 码值转换为对应的字符。

    • 语法：CHAR(n1, n2, ...)
    • 示例：CHAR(72, 101, 108, 108, 111) → 'Hello'

36. CONVERT() / CAST()：转换字符串的字符编码或数据类型。

    • 语法：CONVERT(str USING charset) / CAST(str AS type)
    • 示例：CONVERT('测试' USING utf8mb4)；CAST('123' AS UNSIGNED) → 123

十一、字符串填充函数

37. LPAD()：在字符串左侧用pad_str填充，直到总长度达到length。若原字符串更长，则会被截断。

    • 语法：LPAD(str, length, pad_str)
    • 示例：LPAD('123', 5, '0') → '00123'

38. RPAD()：在字符串右侧用pad_str填充，规则同LPAD。

    • 语法：RPAD(str, length, pad_str)
    • 示例：RPAD('ID', 5, '0') → 'ID000'

十二、其他字符串函数

39. REPEAT()：将字符串重复n次。

    • 语法：REPEAT(str, n)
    • 示例：REPEAT('ab', 3) → 'ababab'

40. REVERSE()：将字符串中的字符顺序完全反转。

    • 语法：REVERSE(str)
    • 示例：REVERSE('abc') → 'cba'

41. STRCMP()：比较两个字符串。相等返回0，str1大于str2返回1，否则返回-1。

    • 语法：STRCMP(str1, str2)
    • 示例：STRCMP('abc', 'abd') → -1

42. FORMAT()：将数字格式化为带千分位分隔符的字符串，并保留指定小数位数。

    • 语法：FORMAT(number, decimal_places)
    • 示例：FORMAT(1234567.89, 2) → '1,234,567.89'

43. UNHEX()：将十六进制字符串转换为对应的二进制字符串（反向函数为 HEX）。

    • 语法：UNHEX(str)
    • 示例：UNHEX('48656C6C6F') → 'Hello'

44. GET_JSON_OBJECT()：从 JSON 格式的字符串中，提取指定路径（$.path）的值（Hive 支持）。

    • 语法：GET_JSON_OBJECT(json_str, '$.path')
    • 示例：GET_JSON_OBJECT('{"name":"Alice", "age":25}', '$.name') → 'Alice'

45. COLLECT_SET + CONCAT_WS()：Hive 中常用的聚合拼接模式，将分组内某列的值去重后，用分隔符拼接成一个字符串（列转行）。

    • 语法：CONCAT_WS(sep, COLLECT_SET(column))
    • 示例：对分组 id 执行 SELECT id, CONCAT_WS(';', COLLECT_SET(subject)) FROM student GROUP BY id，可将同一id的不同科目合并。

从基础的拼接、截取到高级的正则匹配与 JSON 解析，这 45 个 SQL 字符串处理函数构成了数据库文本处理的基石。熟练掌握它们，能够极大提升数据清洗、格式转换和文本分析的效率与灵活性。