SQL单字段拆分查询

单字段拆分查询使用

用法

SUBSTRING_INDEX(val,'|',1) as va1,
SUBSTRING_INDEX(val,'|',-1) as va2

示例1

SELECT * from (SELECT id,SUBSTRING_INDEX(val,'|',1) as va1,
SUBSTRING_INDEX(val,'|',-1) as va2 from `value` )a where id=1

示例2

SELECT * from (SELECT id,SUBSTRING_INDEX(val,'|',1) as t1,
SUBSTRING_INDEX(SUBSTRING_INDEX(val,'|',2),'|',-1) as t2,
SUBSTRING_INDEX(SUBSTRING_INDEX(val,'|',3),'|',-1) as t3,
SUBSTRING_INDEX(val,'|',-1) as t4 from `value` )a where id=2;

示例3

SELECT  
*,
va1 '营业开始时间',
va2 '营业结束时间'

FROM 
(
SELECT  * ,SUBSTRING_INDEX(business_time,'-',1) as va1,SUBSTRING_INDEX(business_time,'-',-1) as va2 FROM  table
) emp