2012年4月11日 星期三

[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)


沒有留言:

張貼留言