MySQL 的几个实用字符串函数

CONCAT() 连接一个或者多个字符串
[sql]
mysql> SELECT CONCAT('a');
+-------------+
| CONCAT('a') |
+-------------+
| a |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('a', 'b', 'c');
+-----------------------+
| CONCAT('a', 'b', 'c') |
+-----------------------+
| abc |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT a, b, CONCAT(a, b) AS c FROM tbl_1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | a | 1a |
| 2 | b | 2b |
| 3 | c | 3c |
| 4 | d | 4d |
+---+------+------+
4 rows in set (0.00 sec)
[/sql]

注： Oracle的 CONCAT() 只能连接两个字符串,MySQL的 CONCAT() 在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
[sql]
mysql> SELECT a, b, c, CONCAT(a, b, c) AS d FROM tbl_2;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | a | a | 1aa |
| 2 | | a | 2a |
| 3 | | b | 3b |
| 4 | c | c | 4cc |
| 5 | NULL | d | NULL |
+---+------+------+------+
5 rows in set (0.00 sec)
[/sql]

但Oracle的 CONCAT() 连接的时候，只要有一个字符串不是NULL,就不会返回NULL


CONCAT_WS() 表示有分隔符的字符串连接
[sql]
mysql> SELECT CONCAT_WS('-', 'a', 'b', 'c');
+-------------------------------+
| CONCAT_WS('-', 'a', 'b', 'c') |
+-------------------------------+
| a-b-c |
+-------------------------------+
1 row in set (0.00 sec)
[/sql]

和 CONCAT() 不同的是, CONCAT_WS() 函数在执行的时候, 不会因为NULL值而返回NULL
[sql]
mysql> SELECT CONCAT_WS('-', 'a', 'b', 'c', NULL);
+-------------------------------------+
| CONCAT_WS('-', 'a', 'b', 'c', NULL) |
+-------------------------------------+
| a-b-c |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT a, b, c, CONCAT_WS('-', a, b, c) AS d FROM tbl_2;
+---+------+------+-------+
| a | b | c | d |
+---+------+------+-------+
| 1 | a | a | 1-a-a |
| 2 | | a | 2--a |
| 3 | | b | 3--b |
| 4 | c | c | 4-c-c |
| 5 | NULL | d | 5-d |
+---+------+------+-------+
5 rows in set (0.00 sec)
[/sql]


GROUP_CONCAT() 可用来行转列
GROUP_CONCAT([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

[sql]
mysql> SELECT * FROM tbl_2;
+----+------+------+
| a | b | c |
+----+------+------+
| 15 | 2 | c |
| 14 | 2 | a |
| 13 | 1 | c |
| 12 | 1 | b |
| 11 | 1 | a |
+----+------+------+
5 rows in set (0.00 sec)
[/sql]

以 b 分组，把 c 字段的值打印在一行，逗号分隔(默认)
[sql]
mysql> SELECT b, GROUP_CONCAT(c) FROM tbl_2 GROUP BY b;
+------+-----------------+
| b | GROUP_CONCAT(c) |
+------+-----------------+
| 1 | c,b,a |
| 2 | c,a |
+------+-----------------+
2 rows in set (0.00 sec)
[/sql]

以 b 分组，把 c 字段的值打印在一行，分号分隔
[sql]
mysql> SELECT b, GROUP_CONCAT(c SEPARATOR ';') FROM tbl_2 GROUP BY b;
+------+-------------------------------+
| b | GROUP_CONCAT(c SEPARATOR ';') |
+------+-------------------------------+
| 1 | c;b;a |
| 2 | c;a |
+------+-------------------------------+
2 rows in set (0.00 sec)
[/sql]

以 b 分组，把去冗余的 c 字段的值打印在一行，逗号分隔
[sql]
mysql> SELECT b, GROUP_CONCAT(DISTINCT c SEPARATOR ';') FROM tbl_2 GROUP BY b;
+------+----------------------------------------+
| b | GROUP_CONCAT(DISTINCT c SEPARATOR ';') |
+------+----------------------------------------+
| 1 | a;b;c |
| 2 | a;c |
+------+----------------------------------------+
2 rows in set (0.00 sec)
[/sql]

以 b 分组，把 c 字段的值打印在一行，逗号分隔, 以 c 排倒序
[sql]
mysql> SELECT b, GROUP_CONCAT(c ORDER BY c DESC) FROM tbl_2 GROUP BY b;
+------+---------------------------------+
| b | GROUP_CONCAT(c ORDER BY c DESC) |
+------+---------------------------------+
| 1 | c,c,b,b,a,a |
| 2 | c,c,a,a |
+------+---------------------------------+
2 rows in set (0.00 sec)
[/sql]


REPEAT() 用来复制字符串
REPEAT('复制的字符串', 复制的份数)
[sql]
mysql> SELECT REPEAT('a', 2);
+----------------+
| REPEAT('a', 2) |
+----------------+
| aa |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT REPEAT('a', 3);
+----------------+
| REPEAT('a', 3) |
+----------------+
| aaa |
+----------------+

mysql> SELECT a, b, REPEAT(b, 3) AS c FROM tbl_1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | a | aaa |
| 2 | b | bbb |
| 3 | c | ccc |
| 4 | d | ddd |
+---+------+------+
4 rows in set (0.00 sec)
[/sql]


SUBSTR() 截取字符串
[sql]
mysql> SELECT * FROM tbl_1 WHERE SUBSTR(b, 1, 3) = 'str';
+----+------+------+
| id | a | b |
+----+------+------+
| 10 | 3 | stri |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTR('string', 1, 3);
+------------------------+
| SUBSTR('string', 1, 3) |
+------------------------+
| str |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTR('string', -1, 3);
+-------------------------+
| SUBSTR('string', -1, 3) |
+-------------------------+
| g |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTR('string', -3, 3);
+-------------------------+
| SUBSTR('string', -3, 3) |
+-------------------------+
| ing |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTR('string', 0, 3);
+------------------------+
| SUBSTR('string', 0, 3) |
+------------------------+
| |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTR('string', 1, -1);
+-------------------------+
| SUBSTR('string', 1, -1) |
+-------------------------+
| |
+-------------------------+
1 row in set (0.00 sec)
[/sql]


UCASE() OR UPPER() 将字符串转为大写
[sql]

mysql> SELECT UCASE('string');
+-----------------+
| UCASE('string') |
+-----------------+
| STRING |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT UCASE(b) FROM tbl_1;
+----------+
| UCASE(b) |
+----------+
| A |
| A |
| B |
| C |
| STRI |
+----------+
5 rows in set (0.00 sec)
[/sql]


LOWER() OR LCASE()
[sql]
mysql> SELECT LOWER('string');
+-----------------+
| LOWER('string') |
+-----------------+
| string |
+-----------------+
1 row in set (0.00 sec)
[/sql]


LOCATE()
[sql]
mysql> SELECT LOCATE('t', 'string');
+-----------------------+
| LOCATE('t', 'string') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
[/sql]


POSITION()
[sql]
mysql> SELECT POSITION('t' IN 'string');
+---------------------------+
| POSITION('t' IN 'string') |
+---------------------------+
| 2 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT POSITION('Ｔ' IN 'ＳＴＲＩＮＧ');
+-----------------------------------------+
| POSITION('Ｔ' IN 'ＳＴＲＩＮＧ') |
+-----------------------------------------+
| 4 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT POSITION('t' IN 'ＳＴＲＩＮＧ');
+---------------------------------------+
| POSITION('t' IN 'ＳＴＲＩＮＧ') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT POSITION('T' IN 'ＳＴＲＩＮＧ');
+---------------------------------------+
| POSITION('T' IN 'ＳＴＲＩＮＧ') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
[/sql]