查找字符串函数

1、返回字符串位置的find_in_set()函数

find_in_set(str1,str2);

该函数会返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中包含若干个用逗号隔开的字符串。

mysql> select find_in_set('mysql','oracle,mysql,db2');
+-----------------------------------------+
| find_in_set('mysql','oracle,mysql,db2') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
1 row in set (0.10 sec)

mysql> select find_in_set('mysql','oracle,wonita,db2');/*返回0，不是返回NULL*/
+------------------------------------------+
| find_in_set('mysql','oracle,wonita,db2') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,mysql   ,db2');/*空格有效*/
+--------------------------------------------+
| find_in_set('mysql','oracle,mysql   ,db2') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,  mysql,db2');/*空格有效*/
+-------------------------------------------+
| find_in_set('mysql','oracle,  mysql,db2') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql','oracle,mysql,db2,mysql');/*返回第一个匹配的位置*/
+-----------------------------------------------+
| find_in_set('mysql','oracle,mysql,db2,mysql') |
+-----------------------------------------------+
|                                             2 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('mysql  ','oracle,mysql  ,db2');/*空格有效*/
+---------------------------------------------+
| find_in_set('mysql  ','oracle,mysql  ,db2') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+
1 row in set (0.00 sec)

2、返回指定字符串位置的field()函数

field(str,str1,str2,...);

该函数返回第一个与字符串str匹配的字符串的位置。

mysql> select field('mysql','oracle','db2','redis','mysql');
+-----------------------------------------------+
| field('mysql','oracle','db2','redis','mysql') |
+-----------------------------------------------+
|                                             4 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select field('mysql','mysql  ','db2','redis');\*字符后面的空格被忽略掉*\
+----------------------------------------+
| field('mysql','mysql  ','db2','redis') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select field('mysql','db2','  mysql','redis','mysql');\*字符前面的空格没有被忽略*\
+------------------------------------------------+
| field('mysql','db2','  mysql','redis','mysql') |
+------------------------------------------------+
|                                              4 |
+------------------------------------------------+
1 row in set (0.00 sec)

3、返回指定位置的字符串的elt()函数

elt(n,str1,str2,...);

mysql> select elt(1,'mysql','db2','oracle');
+-------------------------------+
| elt(1,'mysql','db2','oracle') |
+-------------------------------+
| mysql                         |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select elt(0,'mysql','db2','oracle');/*超出返回范围*/
+-------------------------------+
| elt(0,'mysql','db2','oracle') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select elt(4,'mysql','db2','oracle');/*超出返回范围*/
+-------------------------------+
| elt(4,'mysql','db2','oracle') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select length(elt(1,'mysql  ','db2','oracle'));/*空格被输出*/
+-----------------------------------------+
| length(elt(1,'mysql  ','db2','oracle')) |
+-----------------------------------------+
|                                       7 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select length(elt(1,'  mysql  ','db2','oracle'));/*空格被输出*/
+-------------------------------------------+
| length(elt(1,'  mysql  ','db2','oracle')) |
+-------------------------------------------+
|                                         9 |
+-------------------------------------------+
1 row in set (0.00 sec)