当前位置:Gxlcms > mysql > locate和substring结合实现substring_index功能

locate和substring结合实现substring_index功能

时间:2021-07-01 10:21:17 帮助过:56人阅读

某数据格式如下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 | +-----------------------------+--------+

用法挺灵活,记了一笔!

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

人气教程排行