SQL中表的字符串内置函数详解

SQL 字符串内置函数详解

一、字符串长度与基本信息函数

1. LENGTH / LEN / CHAR_LENGTH

-- 获取字符串字节数 (按字节计算)
SELECT LENGTH('Hello');        -- MySQL/PG: 5
SELECT LEN('Hello');           -- SQL Server: 5

-- 获取字符数 (按字符计算)
SELECT CHAR_LENGTH('Hello');   -- 5
SELECT LENGTH('你好');         -- MySQL: 6 (UTF-8编码)
SELECT CHAR_LENGTH('你好');    -- 2

2. DATALENGTH (SQL Server)

-- 返回表达式使用的字节数
SELECT DATALENGTH('Hello');    -- 5
SELECT DATALENGTH(N'Hello');   -- SQL Server Unicode: 10

二、大小写转换函数

1. UPPER / UCASE

SELECT UPPER('hello world');    -- 'HELLO WORLD'
SELECT UCASE('hello world');    -- MySQL: 'HELLO WORLD'

2. LOWER / LCASE

SELECT LOWER('HELLO WORLD');    -- 'hello world'
SELECT LCASE('HELLO WORLD');    -- MySQL: 'hello world'

三、字符串截取与拼接

1. SUBSTRING / SUBSTR

-- 基本语法：SUBSTRING(string, start, length)
SELECT SUBSTRING('Hello World', 1, 5);    -- 'Hello'
SELECT SUBSTRING('Hello World', 7, 5);    -- 'World'

-- MySQL/PG: 支持负数从末尾开始
SELECT SUBSTRING('Hello World', -5, 5);   -- 'World'

-- 简写形式
SELECT SUBSTR('Hello World', 7);          -- 'World'

2. LEFT / RIGHT

SELECT LEFT('Hello World', 5);   -- 'Hello'
SELECT RIGHT('Hello World', 5);  -- 'World'

3. CONCAT

-- 连接多个字符串
SELECT CONCAT('Hello', ' ', 'World');          -- 'Hello World'

-- MySQL: CONCAT_WS (带分隔符)
SELECT CONCAT_WS(', ', 'John', 'Doe', 'Jr');   -- 'John, Doe, Jr'

-- SQL Server: + 运算符
SELECT 'Hello' + ' ' + 'World';                -- 'Hello World'

四、字符串查找与定位

1. CHARINDEX / INSTR / POSITION

-- SQL Server
SELECT CHARINDEX('World', 'Hello World');      -- 7

-- MySQL
SELECT INSTR('Hello World', 'World');         -- 7
SELECT LOCATE('World', 'Hello World');         -- 7

-- PostgreSQL / 标准SQL
SELECT POSITION('World' IN 'Hello World');    -- 7

2. PATINDEX (SQL Server)

-- 支持模式匹配
SELECT PATINDEX('%orl%', 'Hello World');      -- 8

五、字符串替换函数

1. REPLACE

SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'
SELECT REPLACE('aaa', 'a', 'b');                 -- 'bbb'

2. STUFF (SQL Server)

-- 替换字符串的一部分
SELECT STUFF('Hello World', 7, 5, 'SQL');  -- 'Hello SQL'
-- 参数：原字符串，开始位置，要替换的长度，新字符串

六、去除空白字符

1. TRIM / LTRIM / RTRIM

-- 标准SQL
SELECT TRIM('  Hello World  ');             -- 'Hello World'
SELECT TRIM(LEADING ' ' FROM '  Hello');    -- 'Hello'
SELECT TRIM(TRAILING ' ' FROM 'Hello  ');   -- 'Hello'
SELECT TRIM(BOTH ' ' FROM '  Hello  ');     -- 'Hello'

-- 数据库特定
SELECT LTRIM('  Hello');                    -- 'Hello' (去左空格)
SELECT RTRIM('Hello  ');                    -- 'Hello' (去右空格)

七、填充与格式化函数

1. LPAD / RPAD

-- MySQL/Oracle
SELECT LPAD('123', 5, '0');     -- '00123'
SELECT RPAD('123', 5, '0');     -- '12300'

-- SQL Server (使用RIGHT/LEFT+REPLICATE)
SELECT RIGHT('00000' + '123', 5);           -- '00123'
SELECT LEFT('123' + REPLICATE('0', 5), 5);  -- '12300'

2. REPLICATE / REPEAT

-- SQL Server
SELECT REPLICATE('Hello', 3);   -- 'HelloHelloHello'

-- MySQL
SELECT REPEAT('Hello', 3);      -- 'HelloHelloHello'

八、字符串反转与排序

1. REVERSE

SELECT REVERSE('Hello');        -- 'olleH'

2. ASCII / CHAR

-- 字符与ASCII码转换
SELECT ASCII('A');              -- 65
SELECT CHAR(65);                -- 'A' (SQL Server)
SELECT CHR(65);                 -- 'A' (PG/MySQL)

九、字符串分割与组合

1. STRING_SPLIT (SQL Server 2016+)

SELECT value FROM STRING_SPLIT('apple,banana,orange', ',');
-- 返回三行：apple, banana, orange

2. STRING_AGG (SQL Server 2017+) / GROUP_CONCAT (MySQL)

-- SQL Server
SELECT STRING_AGG(name, ', ') FROM users GROUP BY department_id;

-- MySQL
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users GROUP BY department_id;

-- PostgreSQL
SELECT STRING_AGG(name, ', ') FROM users GROUP BY department_id;

十、高级字符串处理

1. FORMAT (SQL Server 2012+)

-- 格式化字符串
SELECT FORMAT(123456789, 'N');          -- '123,456,789.00'
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd'); -- '2024-01-15'

2. SOUNDEX / DIFFERENCE

-- 语音相似度 (SQL Server)
SELECT SOUNDEX('Smith'), SOUNDEX('Smythe');  -- S530, S530
SELECT DIFFERENCE('Smith', 'Smythe');         -- 4 (0-4，越大越相似)

十一、转义与编码函数

1. QUOTENAME (SQL Server)

-- 添加分隔符
SELECT QUOTENAME('table_name');      -- '[table_name]'
SELECT QUOTENAME('table_name', '"'); -- '"table_name"'

2. ESCAPE 处理

-- 在LIKE中处理特殊字符
SELECT * FROM products 
WHERE name LIKE '%100\% discount%' ESCAPE '\';

十二、JSON处理函数 (现代数据库)

-- MySQL 5.7+/PG 9.4+/SQL Server 2016+
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
SELECT JSON_VALUE('{"name": "John", "age": 30}', '$.name');

十三、跨数据库比较表

函数	MySQL	SQL Server	PostgreSQL	Oracle
长度	LENGTH()	LEN()	LENGTH()	LENGTH()
字符数	CHAR_LENGTH()	-	CHAR_LENGTH()	LENGTH()
位置查找	INSTR()	CHARINDEX()	POSITION()	INSTR()
提取子串	SUBSTRING()	SUBSTRING()	SUBSTRING()	SUBSTR()
连接	CONCAT()	+ 或 CONCAT()	|| 或 CONCAT()	|| 或 CONCAT()
去空格	TRIM()	TRIM()	TRIM()	TRIM()
重复	REPEAT()	REPLICATE()	REPEAT()	RPAD()
反转	REVERSE()	REVERSE()	REVERSE()	-

十四、实用示例集合

-- 1. 提取邮箱域名
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM users;

-- 2. 格式化电话号码
SELECT 
    CASE 
        WHEN LEN(phone) = 10 THEN 
            '(' + SUBSTRING(phone, 1, 3) + ') ' + 
            SUBSTRING(phone, 4, 3) + '-' + 
            SUBSTRING(phone, 7, 4)
        ELSE phone
    END AS formatted_phone
FROM contacts;

-- 3. 分割逗号分隔的值
-- SQL Server
SELECT value 
FROM STRING_SPLIT('red,green,blue', ',');

-- MySQL (需要自定义函数或临时表)

-- 4. 清洗数据：移除多余空格和换行符
SELECT TRIM(REPLACE(REPLACE(column, CHAR(13), ''), CHAR(10), '')) 
FROM table;

-- 5. 生成固定长度的ID
SELECT RIGHT('00000' + CAST(id AS VARCHAR), 6) AS padded_id 
FROM items;

注意事项

字符编码问题：LENGTH在不同编码下返回不同结果 索引位置：多数数据库使用1-based索引，有些函数支持负数 性能考虑：字符串函数在大量数据上可能影响性能 NULL处理：多数函数在输入NULL时返回NULL 跨数据库兼容性：不同DBMS的函数名和参数顺序可能不同

掌握这些字符串函数能极大提高数据处理效率和灵活性。