MySQL 字符串函数

下面是MySql各种内置字符串函数：

char_length(s) 返回字符串的长度，单位是字符个数

length(s)  返回字符串的长度，单位是字节

concat(s1,s2,……) 返回连接参数构成的字符串

concat_ws(x,s1,s2,……) 和concat(s1,s2,……) 一样，不过要加上x

insert(s1,x,len,s2) 用s2代替s1的x位置开始的长度为len的字符串 （不是简单的插入啊）

upper(s), ucase(s)  -->大写

lower(s), lcase(s) -->小写

left(s,n) 返回字符串S开始的前n个字符

right(s,n)   返回字符串S开始的后n个字符 

ltrim(s) 删除开始的空格

rtrim(s) 删除结尾的空格

trim(s)  删除开始和结尾的空格

trim(s1 from s) 删除开始和结尾的s1

strcmp(s) 比较

repeat(s) 重复

replace(s,s1,s2) 代替

mid(s,n,len)  substring(s,n,len)  获取n开始长度是len子串

locate(s1,s)   position(s1 in s)  获取s1在s中的开始位置  （通过下面的例子可以看出mysql中函数说的字符位置是从1开始的）

reverse(s)  反序

field(s,s1,s2……)

find_in_set(s1,s2) 返回s2中与s1匹配的字符串的位置

make_set(x,s1,s2……) 按x的二进制数从s1,s2,s3……中选取字符串

以下是实践：

mysql> select char_length('love'),length('爱');
+---------------------+--------------+
| char_length('love') | length('爱')   |
+---------------------+--------------+
|                   4 |            2 |
+---------------------+--------------+

mysql> select concat('123','567') as part1, concat_ws('0','123','567') as part2;
+--------+---------+
| part1  | part2   |
+--------+---------+
| 123567 | 1230567 |
+--------+---------+

mysql> select insert('12345',1,4,'0');
+-------------------------+
| insert('12345',1,4,'0') |
+-------------------------+
| 05                      |
+-------------------------+

mysql> select upper('aBcD'),ucase('aBcD'),lower('aBcD'),lcase('aBcD');
+---------------+---------------+---------------+---------------+
| upper('aBcD') | ucase('aBcD') | lower('aBcD') | lcase('aBcD') |
+---------------+---------------+---------------+---------------+
| ABCD          | ABCD          | abcd          | abcd          |
+---------------+---------------+---------------+---------------+

mysql> select left('12345678',3),right('12345678',3);
+--------------------+---------------------+
| left('12345678',3) | right('12345678',3) |
+--------------------+---------------------+
| 123                | 678                 |
+--------------------+---------------------+

mysql> select concat('=',ltrim(' 0 12340 '),'='), concat('=',rtrim(' 0 12340 '),'=');
+------------------------------------+------------------------------------+
| concat('=',ltrim(' 0 12340 '),'=') | concat('=',rtrim(' 0 12340 '),'=') |
+------------------------------------+------------------------------------+
| =0 12340 =                         | = 0 12340=                         |
+------------------------------------+------------------------------------+

mysql> select concat('=',trim(' 0 12340 '),'='), concat('=', trim('0' from '012340'),'=');
+-----------------------------------+------------------------------------------+
| concat('=',trim(' 0 12340 '),'=') | concat('=', trim('0' from '012340'),'=') |
+-----------------------------------+------------------------------------------+
| =0 12340=                         | =1234=                                   |
+-----------------------------------+------------------------------------------+

mysql> select repeat('3',3),strcmp('ads','asd'),replace('I learn mysql','learn','love');
+---------------+---------------------+-----------------------------------------+
| repeat('3',3) | strcmp('ads','asd') | replace('I learn mysql','learn','love') |
+---------------+---------------------+-----------------------------------------+
| 333           |                  -1 | I love mysql                            |
+---------------+---------------------+-----------------------------------------+

mysql> select mid('csdn',2,2), substring('csdn',2,2);
+-----------------+-----------------------+
| mid('csdn',2,2) | substring('csdn',2,2) |
+-----------------+-----------------------+
| sd              | sd                    |
+-----------------+-----------------------+

mysql> select locate('sd','csdn'),position('sd' in 'csdn');
+---------------------+--------------------------+
| locate('sd','csdn') | position('sd' in 'csdn') |
+---------------------+--------------------------+
|                   2 |                        2 |
+---------------------+--------------------------+

mysql> select reverse('asdfg');
+------------------+
| reverse('asdfg') |
+------------------+
| gfdsa            |
+------------------+

mysql> select field('as','df','sdsa','as','sfsdf','as');
+-------------------------------------------+
| field('as','df','sdsa','as','sfsdf','as') |
+-------------------------------------------+
|                                         3 |
+-------------------------------------------+

  # 使用find_in_set
mysql> use mysql;
Database changed
mysql> create table student (name varchar(10));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into student values('Elena'),('Demon'),('Stephen');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student where find_in_set('Demon',name);
+-------+
| name  |
+-------+
| Demon |
+-------+
1 row in set (0.05 sec)

mysql> drop table student
    -> ;
Query OK, 0 rows affected (0.08 sec)

mysql> select make_set(10,'11','22','33','44','55','66','77','88','99','1010') as anwser;
+--------+
| anwser |
+--------+
| 22,44  |
+--------+
# 注意： 10化成二进制1010，从低位到高位对应字符串集的s1,s2,s3,s4……  即0101-->'11','22','33','44'->22 44