MySQL字符串函数5

1.返回指定位置的字符串的函数
ELT(N,s1,s2,s3,…,sn)若N = 1，则返回值为字符串1，若N = 2，则返回值为字符串2，一次类推。若N小于1或大于参数的数目，则返回值为NULL。

mysql> select elt(3,'1st','2nd','3rd'),elt(3,'net','os');
+--------------------------+-------------------+
| elt(3,'1st','2nd','3rd') | elt(3,'net','os') |
+--------------------------+-------------------+
| 3rd                      | NULL              |
+--------------------------+-------------------+
1 row in set (0.00 sec)

2.返回指定字符串位置的函数FIELD(s,s1,s2,…)
返回字符串s在s1，s2…中第一次出现的位置，找不到时返回0，如果s为NULL，也返回0。

mysql> select field('Hi','hihi','Hey','Hi','Hii') as col1,
    -> field('Hi','Hey');
+------+-------------------+
| col1 | field('Hi','Hey') |
+------+-------------------+
|    3 |                 0 |
+------+-------------------+
1 row in set (0.00 sec)

3.返回子串位置的函数FIND_IN_SET(s1,s2)
FIND_IN_SET(s1,s2)返回字符串s1在s2中出现的位置，字符串列表是一个由多个逗号‘，’分开的，如果s1不在s2或s2为空字符串，则返回值为0，如果任意一个参数为NULL，则返回值为NULL。

mysql> select find_in_set('Hi','HiiHi,Hi');
+------------------------------+
| find_in_set('Hi','HiiHi,Hi') |
+------------------------------+
|                            2 |
+------------------------------+
1 row in set (0.00 sec)

4.选取字符串的函数MAKE_SET(x,s1,s2,…)
MAKE_SET(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。s1，s2。。。中的NULL值不会被添加到结果中。

mysql> select make_set(1,'a','b','c') as col1,
    -> make_set(1|4,'hello','nice','world') as col2,
    -> make_set(1|4,'hello','nice',NULL,'world') as col3,
    -> make_set(0,'a','b','c') as col4;
+------+-------------+-------+------+
| col1 | col2        | col3  | col4 |
+------+-------------+-------+------+
| a    | hello,world | hello |      |
+------+-------------+-------+------+
1 row in set (0.00 sec)