SQL字符串函数

# SQL字符串函数

作者：哪吒
时间：2024年12月

# 概述

SQL字符串函数用于处理和操作文本数据。这些函数可以帮助我们进行字符串的查找、替换、截取、连接、格式化等操作，是数据处理中非常重要的工具。

# 字符串长度和位置函数

# LENGTH() / CHAR_LENGTH() - 字符串长度

返回字符串的长度。

SELECT LENGTH('Hello World') AS str_length;      -- 结果: 11
SELECT CHAR_LENGTH('你好世界') AS char_length;    -- 结果: 4
SELECT LENGTH('') AS empty_length;               -- 结果: 0
SELECT LENGTH(NULL) AS null_length;              -- 结果: NULL

# LOCATE() / POSITION() - 查找子字符串位置

返回子字符串在主字符串中的位置（从1开始）。

SELECT LOCATE('World', 'Hello World') AS position;     -- 结果: 7
SELECT POSITION('SQL' IN 'MySQL Database') AS pos;     -- 结果: 3
SELECT LOCATE('xyz', 'Hello World') AS not_found;      -- 结果: 0

-- 指定搜索起始位置
SELECT LOCATE('l', 'Hello World', 4) AS position;      -- 结果: 10

# INSTR() - 查找字符串位置

类似LOCATE()，但参数顺序不同。

SELECT INSTR('Hello World', 'World') AS position;      -- 结果: 7
SELECT INSTR('Hello World', 'xyz') AS not_found;       -- 结果: 0

# 字符串截取函数

# SUBSTRING() / SUBSTR() - 截取子字符串

从字符串中截取指定部分。

-- 从指定位置开始截取
SELECT SUBSTRING('Hello World', 7) AS substr;          -- 结果: 'World'
SELECT SUBSTR('Hello World', 1, 5) AS substr;          -- 结果: 'Hello'

-- 负数位置（从右边开始）
SELECT SUBSTRING('Hello World', -5) AS substr;         -- 结果: 'World'
SELECT SUBSTRING('Hello World', -5, 3) AS substr;      -- 结果: 'Wor'

# LEFT() / RIGHT() - 左右截取

从字符串左边或右边截取指定长度。

SELECT LEFT('Hello World', 5) AS left_part;           -- 结果: 'Hello'
SELECT RIGHT('Hello World', 5) AS right_part;         -- 结果: 'World'
SELECT LEFT('Hello World', 0) AS empty_left;          -- 结果: ''

# MID() - 中间截取

从指定位置开始截取指定长度的字符串。

SELECT MID('Hello World', 3, 3) AS middle_part;       -- 结果: 'llo'
SELECT MID('Hello World', 7, 10) AS middle_part;      -- 结果: 'World'

# 字符串连接函数

# CONCAT() - 字符串连接

连接多个字符串。

SELECT CONCAT('Hello', ' ', 'World') AS concatenated;  -- 结果: 'Hello World'
SELECT CONCAT('User: ', 'John', ', Age: ', 25) AS info; -- 结果: 'User: John, Age: 25'
SELECT CONCAT('Hello', NULL, 'World') AS with_null;    -- 结果: NULL

# CONCAT_WS() - 带分隔符连接

使用指定分隔符连接字符串。

SELECT CONCAT_WS('-', '2024', '12', '25') AS date_str; -- 结果: '2024-12-25'
SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Orange') AS fruits; -- 结果: 'Apple, Banana, Orange'
SELECT CONCAT_WS('|', 'A', NULL, 'B', 'C') AS with_null; -- 结果: 'A|B|C' (忽略NULL)

# GROUP_CONCAT() - 分组连接

将分组中的多行数据连接成一个字符串。

-- 示例数据
CREATE TABLE students (
    class_id INT,
    student_name VARCHAR(50)
);

INSERT INTO students VALUES
(1, '张三'), (1, '李四'), (1, '王五'),
(2, '赵六'), (2, '钱七');

-- 按班级分组连接学生姓名
SELECT 
    class_id,
    GROUP_CONCAT(student_name) AS students,
    GROUP_CONCAT(student_name SEPARATOR ' | ') AS students_pipe,
    GROUP_CONCAT(DISTINCT student_name ORDER BY student_name) AS sorted_students
FROM students 
GROUP BY class_id;

# 字符串替换和修改函数

# REPLACE() - 字符串替换

替换字符串中的指定部分。

SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced; -- 结果: 'Hello MySQL'
SELECT REPLACE('Hello Hello', 'Hello', 'Hi') AS replaced;    -- 结果: 'Hi Hi'
SELECT REPLACE('Hello World', 'xyz', 'ABC') AS no_change;    -- 结果: 'Hello World'

# INSERT() - 插入字符串

在指定位置插入字符串。

SELECT INSERT('Hello World', 7, 0, 'Beautiful ') AS inserted; -- 结果: 'Hello Beautiful World'
SELECT INSERT('Hello World', 7, 5, 'MySQL') AS replaced;      -- 结果: 'Hello MySQL'

# REVERSE() - 字符串反转

反转字符串。

SELECT REVERSE('Hello') AS reversed;                          -- 结果: 'olleH'
SELECT REVERSE('12345') AS reversed;                          -- 结果: '54321'

# 大小写转换函数

# UPPER() / UCASE() - 转大写

将字符串转换为大写。

SELECT UPPER('Hello World') AS uppercase;                     -- 结果: 'HELLO WORLD'
SELECT UCASE('mysql database') AS uppercase;                 -- 结果: 'MYSQL DATABASE'

# LOWER() / LCASE() - 转小写

将字符串转换为小写。

SELECT LOWER('Hello World') AS lowercase;                     -- 结果: 'hello world'
SELECT LCASE('MYSQL DATABASE') AS lowercase;                 -- 结果: 'mysql database'

# INITCAP() - 首字母大写

将每个单词的首字母转换为大写（部分数据库支持）。

-- 在MySQL中可以自定义实现
SELECT CONCAT(
    UPPER(LEFT('hello world', 1)),
    LOWER(SUBSTRING('hello world', 2))
) AS initcap;

# 字符串修剪函数

# TRIM() - 去除空格

去除字符串两端的空格或指定字符。

SELECT TRIM('  Hello World  ') AS trimmed;                    -- 结果: 'Hello World'
SELECT TRIM('x' FROM 'xxxHello Worldxxx') AS trimmed;        -- 结果: 'Hello World'
SELECT TRIM(BOTH '0' FROM '000123000') AS trimmed;           -- 结果: '123'

# LTRIM() / RTRIM() - 左右修剪

去除字符串左边或右边的空格。

SELECT LTRIM('  Hello World  ') AS left_trimmed;             -- 结果: 'Hello World  '
SELECT RTRIM('  Hello World  ') AS right_trimmed;            -- 结果: '  Hello World'
SELECT LTRIM(RTRIM('  Hello World  ')) AS both_trimmed;      -- 结果: 'Hello World'

# 字符串填充函数

# LPAD() / RPAD() - 左右填充

在字符串左边或右边填充指定字符到指定长度。

SELECT LPAD('123', 6, '0') AS left_padded;                   -- 结果: '000123'
SELECT RPAD('123', 6, '0') AS right_padded;                  -- 结果: '123000'
SELECT LPAD('Hello', 10, '*') AS left_padded;                -- 结果: '*****Hello'
SELECT RPAD('Hello', 10, '-') AS right_padded;               -- 结果: 'Hello-----'

# 字符串比较函数

# STRCMP() - 字符串比较

比较两个字符串，返回-1、0或1。

SELECT STRCMP('abc', 'abc') AS equal;                        -- 结果: 0
SELECT STRCMP('abc', 'def') AS less_than;                    -- 结果: -1
SELECT STRCMP('def', 'abc') AS greater_than;                 -- 结果: 1

# LIKE - 模式匹配

使用通配符进行模式匹配。

SELECT 'Hello World' LIKE 'Hello%' AS starts_with;          -- 结果: 1 (true)
SELECT 'Hello World' LIKE '%World' AS ends_with;            -- 结果: 1 (true)
SELECT 'Hello World' LIKE '%or%' AS contains;               -- 结果: 1 (true)
SELECT 'Hello World' LIKE 'H_llo%' AS pattern_match;        -- 结果: 1 (true)

# 字符串编码函数

# ASCII() - 获取ASCII码

返回字符的ASCII码值。

SELECT ASCII('A') AS ascii_value;                            -- 结果: 65
SELECT ASCII('a') AS ascii_value;                            -- 结果: 97
SELECT ASCII('0') AS ascii_value;                            -- 结果: 48

# CHAR() - ASCII码转字符

将ASCII码转换为字符。

SELECT CHAR(65) AS char_value;                               -- 结果: 'A'
SELECT CHAR(97) AS char_value;                               -- 结果: 'a'
SELECT CHAR(65, 66, 67) AS multiple_chars;                  -- 结果: 'ABC'

# HEX() / UNHEX() - 十六进制转换

字符串与十六进制之间的转换。

SELECT HEX('Hello') AS hex_value;                            -- 结果: '48656C6C6F'
SELECT UNHEX('48656C6C6F') AS original_string;              -- 结果: 'Hello'

# 实战示例

# 示例1：用户数据处理

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);

INSERT INTO users VALUES
(1, '  张 三  ', 'ZHANG.SAN@EXAMPLE.COM', '138-0000-1234'),
(2, 'li si', 'li.si@example.com', '139-0000-5678'),
(3, 'WANG WU', 'wang.wu@EXAMPLE.COM', '136-0000-9999');

-- 数据清理和格式化
SELECT 
    id,
    TRIM(REPLACE(full_name, '  ', ' ')) AS cleaned_name,
    LOWER(email) AS normalized_email,
    CONCAT(
        SUBSTRING(phone, 1, 3), 
        ' ', 
        SUBSTRING(phone, 5, 4), 
        ' ', 
        SUBSTRING(phone, 10)
    ) AS formatted_phone,
    CONCAT(
        UPPER(LEFT(TRIM(full_name), 1)),
        LOWER(SUBSTRING(TRIM(full_name), 2))
    ) AS proper_case_name
FROM users;

# 示例2：文本分析

-- 创建文章表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT
);

INSERT INTO articles VALUES
(1, 'MySQL数据库教程', 'MySQL是一个关系型数据库管理系统，广泛应用于Web开发中。'),
(2, 'SQL查询优化', 'SQL查询优化是提高数据库性能的重要手段，包括索引优化、查询重写等。');

-- 文本统计分析
SELECT 
    id,
    title,
    LENGTH(content) AS content_length,
    CHAR_LENGTH(content) AS char_count,
    LENGTH(content) - LENGTH(REPLACE(content, '数据库', '')) AS db_mentions,
    CASE 
        WHEN LOCATE('MySQL', content) > 0 THEN 'MySQL相关'
        WHEN LOCATE('SQL', content) > 0 THEN 'SQL相关'
        ELSE '其他'
    END AS category,
    LEFT(content, 50) AS preview
FROM articles;

# 示例3：数据脱敏

-- 敏感数据脱敏
SELECT 
    id,
    full_name,
    -- 邮箱脱敏：保留前3位和@后的域名
    CONCAT(
        LEFT(email, 3),
        '***',
        SUBSTRING(email, LOCATE('@', email))
    ) AS masked_email,
    -- 手机号脱敏：保留前3位和后4位
    CONCAT(
        LEFT(phone, 3),
        '****',
        RIGHT(phone, 4)
    ) AS masked_phone
FROM users;

# 示例4：字符串拆分和重组

-- 处理逗号分隔的标签
CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    tags VARCHAR(200)
);

INSERT INTO posts VALUES
(1, '数据库设计', 'MySQL,数据库,设计,规范'),
(2, 'Web开发', 'HTML,CSS,JavaScript,前端');

-- 标签处理
SELECT 
    id,
    title,
    tags,
    LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1 AS tag_count,
    REPLACE(tags, ',', ' | ') AS formatted_tags,
    UPPER(REPLACE(tags, ',', '_')) AS tag_keywords
FROM posts;

# 性能优化建议

# 1. 避免在WHERE子句中使用字符串函数

-- 不推荐：在WHERE中使用函数
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';

-- 推荐：预处理数据或使用索引
SELECT * FROM users WHERE email = 'john@example.com';

# 2. 使用合适的字符串长度

-- 根据实际需要设置字符串长度
CREATE TABLE users (
    username VARCHAR(50),     -- 而不是VARCHAR(255)
    email VARCHAR(100),       -- 根据邮箱实际长度
    description TEXT          -- 长文本使用TEXT
);

# 3. 批量字符串操作

-- 批量更新而不是逐行处理
UPDATE users 
SET email = LOWER(TRIM(email))
WHERE email REGEXP '[A-Z]|^[[:space:]]|[[:space:]]$';

# 常见错误和解决方案

# 1. 字符编码问题

-- 确保数据库和表使用正确的字符集
CREATE TABLE users (
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

# 2. NULL值处理

-- 处理NULL值
SELECT 
    COALESCE(NULLIF(TRIM(name), ''), '未知') AS safe_name,
    IFNULL(CONCAT(first_name, ' ', last_name), first_name) AS full_name
FROM users;

# 3. 字符串截断

-- 安全截取，避免超出字段长度
SELECT 
    LEFT(long_text, 100) AS preview,
    CASE 
        WHEN LENGTH(long_text) > 100 THEN CONCAT(LEFT(long_text, 97), '...')
        ELSE long_text
    END AS safe_preview
FROM articles;

# 最佳实践

数据清理：在存储前清理和标准化字符串数据
字符集统一：使用UTF-8编码处理多语言文本
长度控制：合理设置字符串字段长度
索引优化：为经常查询的字符串字段创建索引
安全考虑：对用户输入进行适当的转义和验证

# 总结

SQL字符串函数提供了丰富的文本处理能力，合理使用这些函数可以：

高效处理和转换文本数据
实现复杂的字符串操作
提高数据质量和一致性
满足各种文本处理需求

在使用时要注意性能影响、字符编码和NULL值处理，选择最适合的函数和方法来解决具体问题。

聚合函数数值函数

Jeskson-微服务分布式