某数据格式如下a133a-a1729a-a1813aa1134a-a2194aa1098a-a1213a-a1818a需要根据横杠-分段，截取最前面的一段数据。首先想到的是substring_index函数;selectcat_p
某数据格式如下
a133a-a1729a-a1813aa1134a-a2194aa1098a-a1213a-a1818a需要根据横杠'-'分段，，截取最前面的一段数据。
首先想到的是substring_index函数;
select cat_path,substring_index(`cat_path`,'-',1)from pc;+-----------------------------+--------+| cat_path| sl|+-----------------------------+--------+| a1134a-a2194a| a1134a || a1031a-a1039a-a1807a-a2764a | a1031a || a170a-a273a-a2197a-a2203a | a170a || a1098a-a1213a-a1817a| a1098a || a155a-a421a-a491a-a489a| a155a || a155a-a421a-a491a-a494a| a155a || a155a-a421a-a491a-a899a| a155a || a1098a-a1213a-a2150a| a1098a || a155a-a1977a-a483a| a155a || a170a-a273a| a170a |+-----------------------------+--------+偶然看到同事写的一个sql语句也可以实现这个功能
select cat_path,substring(cat_path, 1, locate('a-a',cat_path)) slfrom pc;+-----------------------------+--------+| cat_path| sl|+-----------------------------+--------+| a1134a-a2194a| a1134a || a1031a-a1039a-a1807a-a2764a | a1031a || a170a-a273a-a2197a-a2203a | a170a || a1098a-a1213a-a1817a| a1098a || a155a-a421a-a491a-a489a| a155a || a155a-a421a-a491a-a494a| a155a || a155a-a421a-a491a-a899a| a155a || a1098a-a1213a-a2150a| a1098a || a155a-a1977a-a483a| a155a || a170a-a273a| a170a |+-----------------------------+--------+用法挺灵活，记了一笔！
本文出自 “原下” 博客，请务必保留此出处