mysql中的字符串截取和替换

-- 替换 replace(字段名,"需要替换的字符","替换的字符") mysql里replace不支持正则匹配
mysql> set @needReplaceStr = 'hello world!';
Query OK, 0 rows affected (0.00 sec) mysql> select replace(@needReplaceStr, 'world', 'leyi');
+-------------------------------------------+
| replace(@needReplaceStr, 'world', 'leyi') |
+-------------------------------------------+
| hello leyi! |
+-------------------------------------------+
1 row in set (0.00 sec) — 字符串截取的方式替换 SUBSTRING instr或locate
mysql> set @needReplaceStr = 'hello world!';
Query OK, 0 rows affected (0.00 sec) — 等同于
select concat(SUBSTRING(@needReplaceStr,1, locate(' ',@needReplaceStr)), 'leyi!'); mysql> select concat(SUBSTRING(@needReplaceStr,1, instr(@needReplaceStr,' ')), 'leyi!');
+---------------------------------------------------------------------------+
| concat(SUBSTRING(@needReplaceStr,1, instr(@needReplaceStr,' ')), 'leyi!') |
+---------------------------------------------------------------------------+
| hello leyi! |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec) — left right 截取前几位和后几位字符 mysql> set @needReplaceStr = 'hello world!';
Query OK, 0 rows affected (0.00 sec) mysql> select concat(left(@needReplaceStr, 5), ' leyi!');
+--------------------------------------------+
| concat(left(@needReplaceStr, 5), ' leyi!') |
+--------------------------------------------+
| hello leyi! |
+--------------------------------------------+
1 row in set (0.00 sec) — SUBSTRING_INDEX(str,delim,count)
— 如果count是正数,那么就是从左往右数,第N个分隔符的左边的全部内容
— 如果是负数,那么就是从右边开始数,第N个分隔符右边的所有内容
e.g 截取最后一个逗号之后的内容
select reverse(SUBSTRING_INDEX(reverse('abc,bde,cfg,dfh,ebv'),',',1))
select SUBSTRING_INDEX('abc,bde,cfg,dfh,ebv',',',-1)
上一篇:MySQL substring:字符串截取 (转载)


下一篇:一文搞懂MySQL前缀索引