MySQL常用字符串函数

大写转小写 lower()

mysql>  select lower("ABCDEFG");
+------------------+
| lower("ABCDEFG") |
+------------------+
| abcdefg          |
+------------------+
1 row in set (0.00 sec)

小写转大写 upper()

mysql> select upper("abcdefg");
+------------------+
| upper("abcdefg") |
+------------------+
| ABCDEFG          |
+------------------+
1 row in set (0.00 sec)

字符串的连接 concat()

mysql> select concat("My","S","QL");
+-----------------------+
| concat("My","S","QL") |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)

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

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

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

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

负数代表从右往左数

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

mysql> select length("zhubayi");
+-------------------+
| length("zhubayi") |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> select length("祝八一");
+---------------------+
| length("祝八一")    |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select instr("footer","ter");
+-----------------------+
| instr("footer","ter") |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

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

mysql> select lpad("hello",10,"1");
+----------------------+
| lpad("hello",10,"1") |
+----------------------+
| 11111hello           |
+----------------------+
1 row in set (0.00 sec)

位数不够右边补0 rpad()

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

去除两边的空格 trim()

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

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

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

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

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

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

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

替换字符串 replace()

mysql> select replace('www.mysql.com','w','W');
+----------------------------------+
| replace('www.mysql.com','w','W') |
+----------------------------------+
| WWW.mysql.com                    |
+----------------------------------+
1 row in set (0.00 sec)

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

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

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

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

fromat() 科学技术方式

mysql> select format(129021,4);
+------------------+
| format(129021,4) |
+------------------+
| 129,021.0000     |
+------------------+
1 row in set (0.00 sec)