MySQL基础知识：MySQL String 字符串处理
字符串处理，在任何编程语言中，都是最重要的功能之一；因为在实际编程过程中，基本都会需要处理字符串；SQL也不例外。
处理字符串的关键知识点：
- 使用语言提供的字符串函数；
- 正则表达式。
之前，整理了一个虚拟书店的数据库并虚构了一些书籍的数据，这些数据还是有点乱的。借此练习MySQL字符串的处理。
之前的文章：MySQL基础知识：创建MySQL数据库和表
字符串长度
有两种计算:
- LENGTH(str)：计算所用的
字节数； - CHAR_LENGTH(str): 计算
字符数；
示例：
SET @s = CONVERT('MySQL String Length' USING ucs2);
SELECT CHAR_LENGTH(@s), LENGTH(@s);
和
SET @s = CONVERT('MySQL string length' USING latin1);
SELECT LENGTH(@s), CHAR_LENGTH(@s);
应用示例：
SELECT
name,
author,
IF(
char_length(author) > 10,
concat(LEFT(author, 10), '...'),
author
) as short_author
FROM mysql_practice.book
LIMIT 20;
从字符串中移除字符
TRIM():
TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);
{BOTH|LEADING|TRAILING}默认是 BOTH；[removed_str]]默认是空格。
去除字符串前后空格
select trim(' test string ');
等同于:
select trim(both from ' test string ');
去除字符串前面的空格
select trim(leading from ' test string ');
等同于:
select ltrim(' test string ');
去除字符串末尾的空格
select trim(trailing from ' test string ');
等同于:
select rtrim(' test string ');
去除字符串末尾的 换行符
SELECT
TRIM(TRAILING '\n' FROM field_name) -- \n 为Unix或Linux系统换行符
FROM table_name;
SELECT
TRIM(TRAILING '\r' FROM field_name) -- \r 为MacOS系统换行符
FROM table_name;
SELECT
TRIM(TRAILING '\r\n' FROM field_name)
FROM table_name;
替换字符串
trim(), ltrim, rtrim只能处理字符串开始和结尾的字符；如果要移除字符串中间的字符，可以使用replace()方法。
REPLACE(target_str, str_to_find, str_to_replace);
注意： 这个是字符串方法，MySQL还有一个REPLACE Statement用来insert或update数据的。
支持表达式的replace方法
REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])
官方文档的示例：
mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c |
+-----------------------------------+
mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
+----------------------------------------------------+
| REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) |
+----------------------------------------------------+
| abc def X |
+----------------------------------------------------+
实例:
更新当前book表中publisher字段值:
- 当前的字段值末尾都带有换行符，移除末尾换行符；
- 当前的字段值都带了
加入购物车收藏或加入购物车购买电子书收藏文字，把这些文字都移除掉；
use mysql_practice;
-- 移除末尾的换行符
UPDATE book SET publisher = trim(trailing '\r' from publisher); -- MacOS
-- 移除`加入购物车收藏` 或 `加入购物车购买电子书收藏`
UPDATE book SET publisher = replace(publisher, '加入购物车收藏', '');
UPDATE book SET publisher = replace(publisher, '加入购物车购买电子书收藏', '');
-- 移除前后空格
UPDATE book SET publisher = trim(publisher);
查找、分割(split) 字符串
-
ELT()
-
FIND_IN_SET()
-
INSTR()
-
LOCATE() 和 POSITION()
-
REGEXP_INSTR()
-
REGEXP_SUBSTR()
-
SUBSTR() 和 SUBSTRING()
-
SUBSTRING_INDEX()
-
LEFT()
-
RIGHT()
示例 1
给定一个字符串表示：出版社 + 作者 + 书名 ；如：'电子工业出版社#孙卫琴#Tomcat与Java'
根据分隔符 # 拆分字符串
SET @fullBookName = '电子工业出版社#孙卫琴#Tomcat与Java';
SET @author_delimiter = '#';
SELECT
SUBSTRING_INDEX(@fullBookName, @author_delimiter, 1) as publisher,
SUBSTRING_INDEX(SUBSTRING_INDEX(@fullBookName, @author_delimiter, 2), @author_delimiter, -1) as author,
SUBSTRING_INDEX(SUBSTRING_INDEX(@fullBookName, @author_delimiter, 3), @author_delimiter, -1) as book_name
示例2
给定一个字符串，截取前 n 个字符，并且不截断单词。
输入：
字符串： 'hello world, this is big test str!'
n: 27
输出：
'hello world, this is big'
如果直接使用
select left(@test_str, @n);
返回的结果是： hello world, this is big te，最后单词被截断。
实现步骤:
- 获取@str最左边的@len长度字符串:
LEFT(@str, @len); - 反转获取的字串：
REVERSE(LEFT(@str, @len))； - 找到反转后字串中第一个空格的位置tmpLocation：
LOCATE(' ', REVERSE(LEFT(@str, @len)))；- 注意：:
LOCATE(substring, string, start)；start表示开始的位置，可选，默认是1。没有找到的话，返回0；
- 注意：:
@len - tmpLocation为实际应该截取的位置；
完整代码：
set @str = 'hello world, this is big test str!';
set @len = 8;
SELECT
LEFT(str, cutpos) AS str1,
SUBSTRING(str, cutpos + 1) AS str2
FROM
(
SELECT @test_str AS str, @len - LOCATE(' ', REVERSE(LEFT(@str, @len))) AS cutpos
) s;
示例3
SELECT
productCode,
RIGHT(productCode,
LENGTH(productCode) - INSTR(productCode, '_')) productNo
FROM
products;
拼接字符串
- MySQL字符串连接符是空格：
select 'hello' 'world'；（PostgreSQL和Oracle的是||, SQL Server的是+）； - MySQL还提供了两个拼接字符串的方法：
concatconcat_ws带分割符号
