locate和substring结合实现substring_index功能



某数据格式如下a133a-a1729a-a1813aa1134a-a2194aa1098a-a1213a-a1818a需要根据横杠-分段，截取最前面的一段数据。首先想到的是substring_index函数;SELECTcat_p

某数据格式如下

a133a-a1729a-a1813a a1134a-a2194a a1098a-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)) sl 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 | +-----------------------------+--------+

用法挺灵活，记了一笔！

本文出自 “原下” 博客，请务必保留此出处