mysql 定位截取字符串_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 |

+-----------------------+---------------------+----------------------------------+------------------------------------------------+