1.合并字符串函数CONCAT(s1,s2…),CONCAT_WS(x,s1,s2…)
返回连接参数产生的字符串,参数中含NULL则结果为NULL,含有一个二进制字符串,则结果为一个二进制字符串。
mysql> select concat('My SQL','5.6'),CONCAT('My',NULL,'SQL');
+------------------------+-------------------------+
| concat('My SQL','5.6') | CONCAT('My',NULL,'SQL') |
+------------------------+-------------------------+
| My SQL5.6 | NULL |
+------------------------+-------------------------+
1 row in set (0.00 sec)
CONCAT_WS(x,s1,s2,…) ,CONCAT_WS代表CONCAT With Separator,是CONCAT()的特殊形式,第一个参数x是其他参数的分隔符,分隔符的位置放在要连接的字符串之间。分隔符可以是一个字符串,也可以是其他参数。如果分隔符是NULL,则结果为NULL。函数会忽略任何要连接的为NULL的字符串。
mysql> select concat_ws('-','1st','2nd','3rd'),concat_ws('*','1st',NULL,'3rd');
+----------------------------------+---------------------------------+
| concat_ws('-','1st','2nd','3rd') | concat_ws('*','1st',NULL,'3rd') |
+----------------------------------+---------------------------------+
| 1st-2nd-3rd | 1st*3rd |
+----------------------------------+---------------------------------+
1 row in set (0.00 sec)
2.替换字符串函数INSERT(s1,x,len,s2)
返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度,则返回值为原始字符串。假如len的长度大于其它字符串的长度,则从位置x开始替换。若任何一个参数为NULL,则返回值为NULL。
mysql> select insert('Qeust',2,4,'What') as col1,
-> insert('Quest',-1,4,'What') as col2,
-> insert('Quest',3,100,'Wh') as col3;
+-------+-------+------+
| col1 | col2 | col3 |
+-------+-------+------+
| QWhat | Quest | QuWh |
+-------+-------+------+
1 row in set (0.00 sec)
3.获取制定长度的字符串函数LEFT(s,n)和RIGHT(s,n)
LEFT(s,n)返回字符串s开始的最左边n个字符。
RIGHT(s,n)返回字符串s开始的最右边n个字符。
mysql> select left('football',5);
+--------------------+
| left('football',5) |
+--------------------+
| footb |
+--------------------+
1 row in set (0.00 sec)
mysql> select right('football',4);
+---------------------+
| right('football',4) |
+---------------------+
| ball |
+---------------------+
1 row in set (0.00 sec)