mysql-8.0 常用字符串函数

常用字符串函数

1.char_langth(str)

返回字符串的字符长度.

select sname,char_length(sname) from students;

+-------+--------------------+

| sname | char_length(sname) |

+-------+--------------------+

| Bob   |                  3 |

| Ruth  |                  4 |

| Mike  |                  4 |

| John  |                  4 |

+-------+--------------------+

4 rows in set (0.00 sec)

返回的是字符串的长度,

2.concat(str1,str2,...)

返回括号里面的字符串拼接后的结果.

select concat(sname,"  ",sname) as a from students;

+------------+

| a          |

+------------+

| Bob  Bob   |

| Ruth  Ruth |

| Mike  Mike |

| John  John |

+------------+

返回括号内字符串的拼接后的结果.结果也可以使用别名进行显示.

​

3.ifnull 和concat拼接合用

select concat(sid," ",ifnull(sname,"")) from students;

+----------------------------------+

| concat(sid," ",ifnull(sname,"")) |

+----------------------------------+

| 3 Bob                            |

| 4 Ruth                           |

| 5 Mike                           |

| 6 John                           |

| 7                                |

| 8 Zhang                          |

+----------------------------------+

6 rows in set (0.01 sec)

如果不是用ifnull的话null字段是不进行显示的.为了显示的话可以使用ifnull进行替换成指定字符.

4. 使用指定分隔符进行多个字符串的拼接,一次指定,大家一起使用.

select concat_ws('   ','Frist name','Second name','Last name');

+---------------------------------------------------------+

| concat_ws('   ','Frist name','Second name','Last name') |

+---------------------------------------------------------+

| Frist name   Second name   Last name                    |

+---------------------------------------------------------+

​

5.查询字符串中第一次出现指定字符串的位置

select instr('foobarbar','bar');

+--------------------------+

| instr('foobarbar','bar') |

+--------------------------+

|                        4 |

+--------------------------+

1 row in set (0.00 sec)

这里表示bar第一次显示是在整体字符串的第四个字母处.

6.去掉字符串中最左边的空格ltrim

select ltrim(sname) from students where sid =9;

+--------------+

| ltrim(sname) |

+--------------+

| Peng         |

+--------------+

1 row in set (0.00 sec)

同理 rtrim去除右边空格.

再同理同事去掉两侧的空格的话使用trim即可.

7.将所有str字符串中匹配from_str子串的地方都替换车to_str子字符串.

select replace('www.mysql.com','w','Ww');

+-----------------------------------+

| replace('www.mysql.com','w','Ww') |

+-----------------------------------+

| WwWwWw.mysql.com                  |

+-----------------------------------+

1 row in set (0.00 sec)

8.将字符串中的第n位到第m位的字符串取出

select substring('adasdasdasdas',5,6);

+--------------------------------+

| substring('adasdasdasdas',5,6) |

+--------------------------------+

| dasdas                         |

+--------------------------------+

1 row in set (0.00 sec)

​

9.like模糊匹配

select * from teacher where name like "%a%";

+----+-----------+---------+

| id | name      | dept_id |

+----+-----------+---------+

|  1 | Zhang san |       1 |

|  3 | Wang wu   |       2 |

+----+-----------+---------+

2 rows in set (0.00 sec)

表示name字段包含a这个字符串的.

其中%a_表示以任意字符串开头,并且以倒数第二个字母是a的字符串加任意一个字符串结尾的数据.