mysql 字元串定位、字元串截取

1、locate函數可以實作類似indexof的功能，locate(substr,str)傳回substr子串在字元串str中的位置。

2、substring函數，截取字元串：

substring（str, pos） 

substring（str, pos, length） 

說明：substring（被截取字段，從第幾位開始截取） 

substring（被截取字段，從第幾位開始截取，截取長度） 

3、按關鍵字截取字元串 ：

substring_index（str,delim,count） 

說明：substring_index（被截取字段，關鍵字，關鍵字出現的次數） 

例：select substring_index（"blog.jb51.net"，"."，2） as abstract from my_content_t 

結果：blog.jb51 

mysql> select LOCATE('_',area_code),area_code,substring_index(area_code,'_',1),substring(area_code,1,LOCATE('_',area_code)-1) from rtb_report_area where id<348;
+-----------------------+---------------------+----------------------------------+------------------------------------------------+
| LOCATE('_',area_code) | area_code           | substring_index(area_code,'_',1) | substring(area_code,1,LOCATE('_',area_code)-1) |
+-----------------------+---------------------+----------------------------------+------------------------------------------------+
|                     6 | cn024_0415_[ln_dd]  | cn024                            | cn024                                          |
|                     6 | cn024_0417_[ln_yk]  | cn024                            | cn024                                          |
|                     6 | cn025_0518_[js_lyg] | cn025                            | cn025                                          |
|                     7 | cn0311_0314_[hb_cd] | cn0311                           | cn0311                                         |
|                     7 | cn0551_0559_[ah_hs] | cn0551                           | cn0551                                         |
|                     6 | cn028_0826_[sc_ga]  | cn028                            | cn028                                          |
+-----------------------+---------------------+----------------------------------+------------------------------------------------+