MySQL字符串函数4

1.字符串比较函数STCMP(s1,s2)

mysql> select strcmp('txt','txt2'),strcmp('txt2','txt'),strcmp('txt','txt');
+----------------------+----------------------+---------------------+
| strcmp('txt','txt2') | strcmp('txt2','txt') | strcmp('txt','txt') |
+----------------------+----------------------+---------------------+
|                   -1 |                    1 |                   0 |
+----------------------+----------------------+---------------------+
1 row in set (0.00 sec)

2.获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)
从字符串s中返回一个长度len的子串，起始位置为n，如果n为负值，起始位置为s的末尾向左的n个子串。

mysql> select substring('breakfast', 5) as col1,
    -> substring('breakfast',5,3) as col2,
    -> substring('lunch',-3) as col3,
    -> substring('lunch', -5, 3) as col4;
+-------+------+------+------+
| col1  | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa  | nch  | lun  |
+-------+------+------+------+
1 row in set (0.00 sec)

MID(s,n,len)与SUBSTRING(s,n,len)意义一样。
3.匹配子串开始位置的函数LOCATE(str1, str),POSITION(str1 IN str)和INSTR(str, str1)
3个函数作用相同，返回子字符串str1在字符串str中的开始位置。

mysql> select locate('ball','football'),position('ball'IN'football'),INSTR('football','ball');
+---------------------------+------------------------------+--------------------------+
| locate('ball','football') | position('ball'IN'football') | INSTR('football','ball') |
+---------------------------+------------------------------+--------------------------+
|                         5 |                            5 |                        5 |
+---------------------------+------------------------------+--------------------------+
1 row in set (0.00 sec)

4.字符串逆序函数REVERSE(s)

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)