[MySQL] SUBSTRING_INDEX 字串處理函式

最近因工作開始使用 MySQL ，才發現 MySQL 有內建非常豐富的函式，此例以 SUBSTRING_INDEX 作為代表。更多 String Functions 請參考 http://dev.mysql.com/doc/refman/5.0/en/string-functions.html。

mysql> desc Test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO `Test` (`Name`) VALUES ('A-1'),('A-2'),('A-3'),('B-1'),('B-2'),('C-1');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `Test`;
+------+
| Name |
+------+
| A-1  |
| A-2  |
| A-3  |
| B-1  |
| B-2  |
| C-1  |
+------+
6 rows in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(`Name`,'-',1) AS `TopItem` FROM `Test` GROUP BY `TopItem`;
+---------+
| TopItem |
+---------+
| A       |
| B       |
| C       |
+---------+
3 rows in set (0.00 sec)


mysql> SELECT SUBSTRING_INDEX(`Name`,'-',-1) AS `SubItem` FROM `Test` GROUP BY `SubItem`;
+---------+
| SubItem |
+---------+
| 1       |
| 2       |
| 3       |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(`Name`,'-',-1) AS `BSubItem` FROM `Test` WHERE SUBSTRING_INDEX(`Name`,'-',1) = 'B';
+----------+
| BSubItem |
+----------+
| 1        |
| 2        |
+----------+
2 rows in set (0.00 sec)