MySQL常用字符串函数

最新推荐文章于 2024-07-15 19:44:21 发布

dragon_0826

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量181

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/li826827/article/details/139090747

版权

大写转小写 lower()

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

小写转大写 upper()

mysql> select upper("fdssssssss");
+---------------------+
| upper("fdssssssss") |
+---------------------+
| FDSSSSSSSS          |
+---------------------+
1 row in set (0.01 sec)

字符串的连接 concat()

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

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

mysql> select substr("Hello World", 5);
+--------------------------+
| substr("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)

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

mysql> select length("text");
+----------------+
| length("text") |
+----------------+
|              4 |
+----------------+
1 row in set (0.01 sec)
mysql> select length("你好");
+----------------+
| length("你好") |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

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

mysql> select char_length("text");
+---------------------+
| char_length("text") |
+---------------------+
|                   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, "0");
+------------------------+
| lpad("hello", 10, "0") |
+------------------------+
| 00000hello             |
+------------------------+
1 row in set (0.00 sec)

位数不够右边补0 rpad()

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

去除两边的空格 trim()

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

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

mysql> select trim(leading 'x' from 'xxxxxtrimleadingxxxxx');
+------------------------------------------------+
| trim(leading 'x' from 'xxxxxtrimleadingxxxxx') |
+------------------------------------------------+
| trimleadingxxxxx                               |
+------------------------------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> select trim(both 'x' from 'xxxxxxtrimbothxxxxx');
+-------------------------------------------+
| trim(both 'x' from 'xxxxxxtrimbothxxxxx') |
+-------------------------------------------+
| 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('abcdefg');
+--------------------+
| reverse('abcdefg') |
+--------------------+
| gfedcba            |
+--------------------+
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)