45個常用SQL字符串處理函數(shù)的基礎(chǔ)語法
|
admin
2025年8月30日 12:50
本文熱度 513
|
在數(shù)據(jù)處理與分析中，字符串操作是不可或缺的環(huán)節(jié)。無論是清洗繁雜的文本數(shù)據(jù)、提取關(guān)鍵信息，還是格式化輸出結(jié)果，都離不開 SQL 字符串處理函數(shù)。SQL 字符串處理函數(shù)覆蓋了拼接、截取、替換、查找等多種應(yīng)用場景，適配 MySQL、Oracle、Hive 等主流數(shù)據(jù)庫，卻因SQL語法細節(jié)和功能差異常讓我們混淆。下面整理了 45 個常用 SQL 字符串處理函數(shù)，從基礎(chǔ)的大小寫轉(zhuǎn)換到復(fù)雜的正則匹配、JSON 解析，一一簡單介紹其作用、語法結(jié)構(gòu)及應(yīng)用示例，僅供參考。 一、字符串拼接函數(shù)1、CONCAT()：將多個字符串拼接為一個字符串，任何參數(shù)為NULL則返回NULL。 - 語法：
CONCAT(str1, str2, ...) - 示例：
CONCAT('SQL', ' ', '字符串') → 'SQL 字符串'
2、CONCAT_WS()：使用指定分隔符拼接字符串，忽略NULL值。 - 語法：
CONCAT_WS(separator, str1, str2, ...) - 示例：
CONCAT_WS('-', '2023', '10', NULL, '05') → '2023-10-05'
3、|| 運算符：字符串拼接（部分?jǐn)?shù)據(jù)庫支持，如Oracle、PostgreSQL）。 - 示例：
'Hello' || ' ' || 'World' → 'Hello World'
二、字符串長度與測量函數(shù)4、LENGTH() / LEN()：返回字符串的字節(jié)數(shù)（LENGTH）或字符數(shù)（LEN，SQL Server）。 - 語法：
LENGTH(str) / LEN(str) - 示例：
LENGTH('中國') → 2（GBK編碼）；LEN('abc') → 3
5、CHAR_LENGTH() / CHARACTER_LENGTH()：返回字符串的字符數(shù)（忽略編碼，按字符計數(shù)）。 - 示例：
CHAR_LENGTH('中國abc') → 5
6、BIT_LENGTH()：返回字符串的比特數(shù)（1字節(jié)=8比特）。 三、字符串截取與提取函數(shù)7、SUBSTRING() / SUBSTR()：從指定位置截取指定長度的子串（start為正數(shù)從左開始，負數(shù)從右開始）。 - 語法：
SUBSTRING(str, start, length) / SUBSTR(str, start, length) - 示例：
SUBSTRING('SQL字符串', 2, 3) → 'QL字'；SUBSTR('數(shù)據(jù)庫', -2) → '據(jù)庫'
8、LEFT()：返回字符串左側(cè)指定長度的子串。 - 示例：
LEFT('數(shù)據(jù)分析', 2) → '數(shù)據(jù)'
9、RIGHT()：返回字符串右側(cè)指定長度的子串。 - 示例：
RIGHT('Python', 3) → 'hon'
10、SUBSTRING_INDEX()：按分隔符截取子串，count為正數(shù)取左側(cè)第count個分隔符前的內(nèi)容，負數(shù)取右側(cè)。 - 語法：
SUBSTRING_INDEX(str, delimiter, count) - 示例：
SUBSTRING_INDEX('a.b.c.d', '.', 2) → 'a.b'；SUBSTRING_INDEX('a.b.c.d', '.', -2) → 'c.d'
四、字符串替換與修改函數(shù)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一一對應(yīng)）。 - 語法：
TRANSLATE(str, from_str, to_str) - 示例：
TRANSLATE('123-abc', '1a', 'Xx') → 'X23-xbc'
14、REGEXP_REPLACE()：用指定字符串替換匹配正則表達式的部分。 - 語法：
REGEXP_REPLACE(str, regex, replace_str) - 示例：
REGEXP_REPLACE('a1b2c3', '[0-9]', '*') → 'a*b*c*'；REGEXP_REPLACE('hello_2023_world', '_(\\d+)_', '-') → 'hello-2023-world'
五、大小寫轉(zhuǎn)換函數(shù)15、UPPER() / UCASE()：將字符串轉(zhuǎn)為大寫。 - 語法：
UPPER(str) / UCASE(str) - 示例：
UPPER('Hello') → 'HELLO'
16、LOWER() / LCASE()：將字符串轉(zhuǎn)為小寫。 - 語法：
LOWER(str) / LCASE(str) - 示例：
LOWER('WORLD') → 'world'
17、INITCAP()：將每個單詞的首字母轉(zhuǎn)為大寫，其余小寫（Oracle、PostgreSQL支持）。 - 示例：
INITCAP('hello world') → 'Hello World'；initcap('hello world sql') → 'Hello World Sql'
六、空格處理函數(shù)18、LTRIM()：去除字符串左側(cè)空格。 - 示例：
LTRIM(' abc ') → 'abc '
19、RTRIM()：去除字符串右側(cè)空格。 - 示例：
RTRIM(' abc ') → ' abc'
20、TRIM()：去除指定位置的指定字符（默認(rèn)兩側(cè)空格）。 - 語法：
TRIM([BOTH/LEADING/TRAILING] trim_str FROM str)（默認(rèn)去除兩側(cè)空格） - 示例：
TRIM('x' FROM 'xxabcxx') → 'abc'；TRIM(LEADING ' ' FROM ' test') → 'test'
21、SPACE()：生成n個空格的字符串。 - 示例：
CONCAT('a', SPACE(2), 'b') → 'a b'
七、字符串查找與定位函數(shù)22、LOCATE() / POSITION()：返回子串在字符串中首次出現(xiàn)的位置（從1開始，未找到返回0）。 - 語法：
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()：返回子串在字符串中首次出現(xiàn)的位置（Oracle等數(shù)據(jù)庫，與LOCATE參數(shù)順序相反）。 - 示例：
INSTR('abcde', 'cd') → 3；INSTR('hello world', 'o') → 5
24、FIELD()：返回str在后續(xù)字符串列表中的位置（未找到返回0）。 - 語法：
FIELD(str, str1, str2, ...) - 示例：
FIELD('b', 'a', 'b', 'c') → 2
25、FIND_IN_SET()：返回str在逗號分隔的字符串列表中的位置（未找到返回0）。 - 語法：
FIND_IN_SET(str, str_list) - 示例：
FIND_IN_SET('b', 'a,b,c,d') → 2；FIND_IN_SET('mysql', 'hive,mysql,spark') → 2
八、字符串拆分與合并函數(shù)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) - 示例：對表中
name列('a','b','c')執(zhí)行STRING_AGG(name, ';') → 'a;b;c'
28、REGEXP_SPLIT_TO_TABLE()：按正則表達式拆分字符串為多行（PostgreSQL支持）。 - 語法：
REGEXP_SPLIT_TO_TABLE(str, regex) - 示例：
REGEXP_SPLIT_TO_TABLE('a,b;c', '[;,]') → 返回3行：a、b、c
29、SPLIT()（部分?jǐn)?shù)據(jù)庫支持，如Hive、PostgreSQL）：按分隔符將字符串拆分為數(shù)組。 - 示例：
SPLIT('a,b,c,d', ',') → ['a', 'b', 'c', 'd']（數(shù)組格式）
30、split_part()（Hive等支持）：按分隔符拆分字符串后，返回指定位置的子串（位置從1開始）。 - 語法：
split_part(str, delimiter, position) - 示例：
split_part('a,b,c,d', ',', 3) → 'c'
九、正則表達式函數(shù)31、REGEXP_LIKE()：判斷字符串是否匹配正則表達式（返回布爾值或1/0）。 - 語法：
REGEXP_LIKE(str, regex) - 示例：
REGEXP_LIKE('123abc', '^[0-9]+$') → 0（不匹配純數(shù)字）
32、REGEXP_SUBSTR()：返回字符串中匹配正則表達式的子串。 - 語法：
REGEXP_SUBSTR(str, regex) - 示例：
REGEXP_SUBSTR('abc123def', '[0-9]+') → '123'；REGEXP_SUBSTR('a1b2c3d4', '[a-z]', 1, 3) → 'c'（從位置1開始，提取第3個小寫字母）
33、regexp_extract()（Hive支持）：通過正則表達式提取指定分組的內(nèi)容（group_index=0返回整個匹配，1返回第一個分組）。 - 語法：
regexp_extract(str, regex, group_index) - 示例：
regexp_extract('user_123_name', 'user_(\\d+)_name', 1) → '123'
十、字符編碼與轉(zhuǎn)換函數(shù)34、ASCII()：返回字符串首字符的 ASCII 碼值（僅對單字節(jié)字符有效，多字節(jié)字符返回首字節(jié)的編碼值，非完整字符編碼）。 - 示例：ASCII('A') → 65（單字節(jié)字符，直接返回 ASCII 碼）；ASCII('中') → 178（GBK 編碼下首字節(jié)值，編碼不同結(jié)果不同）；若需獲取中文的 Unicode 編碼，應(yīng)使用UNICODE('中') → 20013
35、CHAR()：將ASCII碼轉(zhuǎn)為對應(yīng)字符。 - 示例：
CHAR(65, 66) → 'AB'；CHAR(72, 101, 108, 108, 111) → 'Hello'
36、CONVERT() / CAST()：轉(zhuǎn)換字符串編碼（CONVERT）或數(shù)據(jù)類型（CAST）。 - 語法：
CONVERT(str USING charset) / CAST(str AS type) - 示例：
CONVERT('測試' USING utf8) → 按utf8編碼轉(zhuǎn)換；CAST('123' AS UNSIGNED) → 123
十一、字符串填充函數(shù)37、LPAD()：在字符串左側(cè)用指定字符填充，直到達到指定長度（若原字符串長度超過指定長度，則截斷）。 - 語法：
LPAD(str, length, pad_str) - 示例：
LPAD('123', 5, '0') → '00123'；LPAD('abcdef', 4, 'x') → 'abcd'
38、RPAD()：在字符串右側(cè)用指定字符填充，規(guī)則同LPAD。 - 語法：
RPAD(str, length, pad_str) - 示例：
RPAD('ID', 5, '0') → 'ID000'
十二、其他字符串函數(shù)39、REPEAT()：將字符串重復(fù)n次。 - 示例：
REPEAT('ab', 3) → 'ababab'
40、REVERSE()：反轉(zhuǎn)字符串。 - 示例：
REVERSE('abc') → 'cba'；REVERSE('reverse') → 'esrever'
41、STRCMP()：比較兩個字符串（相等返回0，str1>str2返回1，否則返回-1）。 - 示例：
STRCMP('abc', 'abd') → -1
42、FORMAT()：將數(shù)字格式化為帶千分位的字符串，并保留指定小數(shù)位數(shù)。 - 語法：
FORMAT(number, decimal_places) - 示例：
FORMAT(1234567.89, 2) → '1,234,567.89'
43、UNHEX()：將十六進制字符串轉(zhuǎn)換為二進制字符串（反向為HEX函數(shù)）。 - 示例：
UNHEX('48656C6C6F') → 'Hello'
44、GET_JSON_OBJECT()（Hive支持）：從JSON字符串中提取指定路徑的值。 - 語法：
GET_JSON_OBJECT(json_str, '$.path') - 示例：
GET_JSON_OBJECT('{"name":"Alice", "age":25}', '$.name') → 'Alice'
45、COLLECT_SET + CONCAT_WS()（Hive聚合拼接）：將分組內(nèi)的列值去重后，用sep拼接成字符串（常用于“列轉(zhuǎn)行”）。 - 語法：
CONCAT_WS(sep, COLLECT_SET(column)) - 示例：若表
student有數(shù)據(jù)(1, 'math'), (1, 'english'), (2, 'math')，則SELECT id, CONCAT_WS(';', COLLECT_SET(subject)) AS subjects FROM student GROUP BY id → 結(jié)果：(1, 'math;english'), (2, 'math')
以上 SQL 字符串處理函數(shù)，從簡單的空格清理到復(fù)雜的正則提取、JSON 解析，每一類函數(shù)都有其獨特價值。不同數(shù)據(jù)庫對函數(shù)的支持存在細微差異（如 Hive 的GET_JSON_OBJECT、PostgreSQL 的REGEXP_SPLIT_TO_TABLE），實際使用時需結(jié)合具體場景和數(shù)據(jù)庫特性選擇。掌握這些函數(shù)不僅能簡化數(shù)據(jù)清洗流程，更能解鎖復(fù)雜文本分析的可能性。
閱讀原文：原文鏈接
該文章在 2025/9/1 12:18:58 編輯過
|
|
400 186 1886
