MySQL 你没见过的字符串函数

阅读本文大概需要 5 分钟。

来自：胖虎编程

抓紧get新技能吧~

MySQL常用字符串函数

大写转小写 lower()

mysql>  select lower("KFDSKGS");
+------------------+
| lower("KFDSKGS") |
+------------------+
| kfdskgs |
+------------------+

小写转大写 upper()

mysql>  select upper("fdssssssss");
+-------------------+
| upper("fdssssssss") |
+-------------------+
| FDSSSSSSSS |
+-------------------+

字符串的连接 concat()

mysql>  select concat("My","S","QL");
+--------------------+
| concat("My","S","QL") |
+--------------------+
| MySQL |
+--------------------+

截取字符串 substr() / substring()

mysql>  select substring("Hello world",5);
+-----------------------+
| substring("Hello world",5) |
+-----------------------+
| o world |
+-----------------------+
1 row in set (0.07 sec)

mysql>  select substring("Hhllo world",5,3);
+-------------------------+
| substring("Hhllo world",5,3) |
+-------------------------+
| o w |
+-------------------------+
1 row in set (0.07 sec)

mysql>  select substring("Hhllo world",-5);
+------------------------+
| substring("Hhllo world",-5) |
+------------------------+
| world |
+------------------------+
1 row in set (0.08 sec)

查看字符串的长度 length() 这里的UTF8 中文是占用两个字节

mysql>  select length("text");
+-------------+
| length("text") |
+-------------+
| 4 |
+-------------+

mysql>  select length("你好");
+--------------+
| length("你好") |
+--------------+
| 6 |
+--------------+

返回一共有多少个字 char_length() 几个字母就是返回多少

mysql>  select char_length("text");
+------------------+
| char_length("text") |
+------------------+
| 4 |
+------------------+
1 row in set (0.07 sec)

返回字串第一次出现的位置 instr()

mysql>  select instr("footer","ter");
+-----------------+
| instr("footer","ter") |
+-----------------+
| 4 |
+-----------------+

如果不够指定的位数则做什么操作 lpad()

mysql>  select lpad("hello",10,"0");
+-----------------+
| lpad("hello",10,"0") |
+-----------------+
| 00000hello |
+-----------------+
1 row in set (0.13 sec)

位数不够右边补0 rpad()

mysql>  select rpad("hello",10,"0");
+------------------+
| rpad("hello",10,"0") |
+------------------+
| hello00000 |
+------------------+
1 row in set (0.13 sec)

去除两边的空格 trim ()

mysql>  select trim(' bar ');
+-------------+
| trim(' bar ') |
+-------------+
| bar |
+-------------+
1 row in set (0.13 sec)

指定字符串截取 左边截取字符串 trim()

mysql>  select trim(leading 'X' from 'XXXXXtrimleadingXXXX');
+---------------------------------------+
| trim(leading 'X' from 'XXXXXtrimleadingXXXX') |
+---------------------------------------+
| trimleadingXXXX |
+---------------------------------------+
1 row in set (0.13 sec)

指定去除字符串右边的xyz,左边截取字符串 trim()

mysql>  select trim(trailing 'zyz' from 'barxxyz');
+----------------------------+
| trim(trailing 'zyz' from 'barxxyz') |
+----------------------------+
| barxxyz |
+----------------------------+
1 row in set (0.12 sec)

指定去除字符串两边的X trim()

mysql>  trim(both 'X' from 'XXXXXXXtrimbothXXXXXXX')
+----------------------------------------+
| trim(both 'X' from 'XXXXXXXtrimbothXXXXXXX') |
+----------------------------------------+
| trimboth |
+----------------------------------------+
1 row in set (0.13 sec)

替换字符串 replace()

mysql>  select replace('www.mysql.com','w','W');
+------------------------------+
| replace('www.mysql.com','w','W') |
+------------------------------+
| WWW.mysql.com |
+------------------------------+

repeat(str,count) 将字符串str重复count次后返回

mysql>  select repeat('MySQL',3);
+--------------------+
| repeat('MySQL',3) |
+--------------------+
| MySQLMySQLMySQL |
+--------------------+
1 row in set (0.07 sec)

reverse() 将字符串反转后返回

mysql>  select reverse("abcdef");
+-----------------+
| reverse("abcdef") |
+-----------------+
| fedcba |
+-----------------+
1 row in set (0.06 sec)

fromat() 科学技术方式

mysql>  select format(129021,4);
+----------------+
| format(129021,4) |
+----------------+
| 129,021.0000 |
+----------------+

完结~