SQL字符串处理核心指南：从拼接、截取到正则匹配与JSON解析的45个函数详解

在数据清洗、文本分析与报表生成等数据处理工作中，字符串操作是每个SQL使用者无法绕开的必备技能。面对来自不同业务系统的繁杂数据，我们常常需要拼接、截取、替换或搜索特定文本信息。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() 返回字符串的字节数（byte length）。LEN() 是 SQL Server 中的函数，返回字符串的字符数（character length）。

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

5、CHAR_LENGTH() / CHARACTER_LENGTH()

返回字符串的字符数，不受字符编码影响，按Unicode字符计数。

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

6、BIT_LENGTH()

返回字符串的比特数（bit length）。1字节等于8比特。

• 语法：BIT_LENGTH(str)
• 示例：BIT_LENGTH('a') → 8

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

7、SUBSTRING() / SUBSTR()

从指定位置 (start) 开始，截取指定长度 (length) 的子字符串。start 为正数时从左开始计数（从1开始），为负数时从右开始计数。

• 语法：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 中的字符一一对应，将 str 中出现在 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*'；REGEXP_REPLACE('hello_2023_world', '_(\\d+)_', '-') → 'hello-2023-world'

五、大小写转换函数

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') → 'Hello World'；initcap('hello world sql') → 'Hello World Sql'

六、空格处理函数

18、LTRIM()

去除字符串左侧的空白字符（空格、制表符等）。

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

19、RTRIM()

去除字符串右侧的空白字符。

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

20、TRIM()

去除字符串两侧（默认）或指定位置（LEADING 左侧，TRAILING 右侧）的指定字符（默认是空格）。

• 语法：TRIM([BOTH/LEADING/TRAILING] trim_str FROM str) （默认去除两侧空格）
• 示例：TRIM('x' FROM 'xxabcxx') → 'abc'；TRIM(LEADING ' ' FROM '  test') → 'test'

21、SPACE()

生成一个由 n 个空格组成的字符串。

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

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

22、LOCATE() / POSITION()

返回子串 substr 在字符串 str 中首次出现的位置（从1开始计数）。如果未找到，则返回0。LOCATE 支持从指定位置 (start) 开始查找。

• 语法：LOCATE(substr, str[, start]) / POSITION(substr IN str)
• 示例：LOCATE('ab', 'aababc') → 2；POSITION('cd' IN 'abc') → 0；LOCATE('ab', 'aabab', 2) → 4（从位置2开始查找）

23、INSTR()

功能与 LOCATE 类似，但参数顺序不同（str 在前，substr 在后）。主要在 Oracle 等数据库中使用。

• 语法：INSTR(str, substr)
• 示例：INSTR('abcde', 'cd') → 3；INSTR('hello world', 'o') → 5

24、FIELD()

返回字符串 str 在后续字符串列表 (str1, str2, ...) 中的位置索引（从1开始）。如果未找到，则返回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('b', 'a,b,c,d') → 2；FIND_IN_SET('mysql', 'hive,mysql,spark') → 2

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

26、SPLIT_STR()

按分隔符 (delimiter) 拆分字符串 str，并返回第 pos 个元素（MySQL中可用，但非标准函数）。

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

27、STRING_AGG()

一个聚合函数，将分组内多行的字符串表达式 (expr) 用指定的分隔符 (separator) 合并成单个字符串。常见于 SQL Server、PostgreSQL 等。

• 语法：STRING_AGG(expr, separator)
• 示例：对表中 name 列值 ('a','b','c') 执行 STRING_AGG(name, ';') → 'a;b;c'

28、REGEXP_SPLIT_TO_TABLE()

按正则表达式 regex 将字符串 str 拆分成多行结果（PostgreSQL 支持）。这为复杂的文本拆分提供了强大支持。

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

29、SPLIT()

按分隔符将字符串拆分为数组（Hive、PostgreSQL 等支持）。

• 语法：SPLIT(str, separator)
• 示例：SPLIT('a,b,c,d', ',') → ['a', 'b', 'c', 'd']（数组格式）

30、split_part()

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

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

九、正则表达式函数

正则表达式是处理复杂文本模式的瑞士军刀，掌握以下函数能极大提升你的数据清洗能力。

31、REGEXP_LIKE()

判断字符串 str 是否匹配给定的正则表达式 regex。通常返回布尔值（True/False）或1/0。

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

32、REGEXP_SUBSTR()

返回字符串 str 中匹配正则表达式 regex 的第一个子串。某些数据库支持指定开始位置和匹配次数的参数。

• 语法：REGEXP_SUBSTR(str, regex)
• 示例：REGEXP_SUBSTR('abc123def', '[0-9]+') → '123'；REGEXP_SUBSTR('a1b2c3d4', '[a-z]', 1, 3) → 'c'（从位置1开始，提取第3个小写字母）

33、regexp_extract()

通过正则表达式提取指定分组 (group_index) 的内容（Hive 支持）。group_index 为0时返回整个匹配，为1时返回第一个括号分组的内容，以此类推。

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

十、字符编码与转换函数

34、ASCII()

返回字符串第一个字符的 ASCII 码值。注意：对于多字节字符（如中文），返回的是其编码的首字节值，并非完整的 Unicode 码点。

• 语法：ASCII(str)
• 示例：ASCII('A') → 65；ASCII('中') 的结果取决于数据库字符编码；如需Unicode码点，应使用 UNICODE('中') → 20013。

35、CHAR()

将一个或多个 ASCII 码值转换为其对应的字符，并拼接成字符串。

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

36、CONVERT() / CAST()

CONVERT 常用于转换字符串的字符集编码；CAST 用于转换数据的类型。

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

十一、字符串填充函数

37、LPAD()

在字符串 str 的左侧使用 pad_str 进行填充，直到字符串总长度达到 length。如果原字符串长度已超过 length，则会被截断。

• 语法：LPAD(str, length, pad_str)
• 示例：LPAD('123', 5, '0') → '00123'；LPAD('abcdef', 4, 'x') → 'abcd'

38、RPAD()

在字符串 str 的右侧使用 pad_str 进行填充，规则与 LPAD 相同。

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

十二、其他实用字符串函数

39、REPEAT()

将字符串 str 重复 n 次。

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

40、REVERSE()

将字符串 str 的字符顺序完全反转。

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

41、STRCMP()

比较两个字符串 str1 和 str2。如果相等返回0；如果 str1 大于 str2 返回1；否则返回-1。

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

42、FORMAT()

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

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

43、UNHEX()

将十六进制字符串 str 转换为其对应的二进制字符串（ASCII文本）。反向函数是 HEX()。

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

44、GET_JSON_OBJECT()

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

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

45、COLLECT_SET + CONCAT_WS()

这是一个 Hive 中常用的组合技，用于实现“列转行”聚合。COLLECT_SET 将分组内的某列值去重后收集为集合，CONCAT_WS 再将该集合用分隔符合并为字符串。

• 语法：CONCAT_WS(sep, COLLECT_SET(column))
• 示例：若 student 表有数据 (1, 'math'), (1, 'english'), (2, 'math')，则执行 SELECT id, CONCAT_WS(';', COLLECT_SET(subject)) AS subjects FROM student GROUP BY id → 结果：(1, 'math;english'), (2, 'math')

总结

从最基础的 CONCAT 拼接、TRIM 清理，到功能强大的 REGEXP_REPLACE 正则替换和 GET_JSON_OBJECT JSON解析，这45个函数构成了SQL字符串处理的工具箱。掌握它们，意味着你能够更高效、更优雅地应对各种数据清洗和文本格式化任务。

需要特别注意的是，不同数据库或数据平台（如 MySQL、PostgreSQL、Hive、Oracle）对这些函数的命名、语法细节和支持程度存在差异。在实际项目中使用时，务必查阅对应数据库的官方文档进行确认。熟练运用这些函数，不仅能简化你的SQL代码，更能解锁深层次文本分析与处理的可能性，是每个数据工作者必备的核心技能。

如果你想深入探讨某个函数的具体应用或分享你的使用心得，欢迎在技术社区进行交流。