mysql 字符串分割

前言

略

substring_index

正向截取字符串

mysql> select substring_index('www.baidu.com','.',1);
+----------------------------------------+
| substring_index('www.baidu.com','.',1) |
+----------------------------------------+
| www                                    |
+----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',2);
+----------------------------------------+
| substring_index('www.baidu.com','.',2) |
+----------------------------------------+
| www.baidu                              |
+----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',3);
+----------------------------------------+
| substring_index('www.baidu.com','.',3) |
+----------------------------------------+
| www.baidu.com                          |
+----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',4);
+----------------------------------------+
| substring_index('www.baidu.com','.',4) |
+----------------------------------------+
| www.baidu.com                          |
+----------------------------------------+
1 row in set (0.02 sec)

逆向截取字符串

mysql> select substring_index('www.baidu.com','.',-1);
+-----------------------------------------+
| substring_index('www.baidu.com','.',-1) |
+-----------------------------------------+
| com                                     |
+-----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',-2);
+-----------------------------------------+
| substring_index('www.baidu.com','.',-2) |
+-----------------------------------------+
| baidu.com                               |
+-----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',-3);
+-----------------------------------------+
| substring_index('www.baidu.com','.',-3) |
+-----------------------------------------+
| www.baidu.com                           |
+-----------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index('www.baidu.com','.',-4);
+-----------------------------------------+
| substring_index('www.baidu.com','.',-4) |
+-----------------------------------------+
| www.baidu.com                           |
+-----------------------------------------+
1 row in set (0.02 sec)

substring_index 特性

1. substring_index 是 substring 的升级版，它是通过字符串分割后的数组的索引换算成截取位置进行字符串截取。
2. substring_index 不能直接获取字符串分割后的数组中的某项，可以与其它函数组合后实现。

字符串分割

mysql> select substring_index('www.baidu.com', '.', 1);
+------------------------------------------+
| substring_index('www.baidu.com', '.', 1) |
+------------------------------------------+
| www                                      |
+------------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index(substring_index('www.baidu.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| substring_index(substring_index('www.baidu.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| baidu                                                              |
+--------------------------------------------------------------------+
1 row in set (0.02 sec)mysql> select substring_index(substring_index('www.baidu.com', '.', 3), '.', -1);
+--------------------------------------------------------------------+
| substring_index(substring_index('www.baidu.com', '.', 3), '.', -1) |
+--------------------------------------------------------------------+
| com                                                                |
+--------------------------------------------------------------------+
1 row in set (0.02 sec)