MYSQL不规则字符串提取时间

1、数据格式如下

处理结果：

代码如下:

select str_to_date(left(date,10),'%Y-%m-%d') as d,name as ha,
date_format(concat(str_to_date(left(date,10),'%Y-%m-%d'),
'-',sb1_time),'%H:%i') as kk_sb1,
date_format(concat(str_to_date(left(date,10),
'%Y-%m-%d'),'-',sb4_time),'%H:%i') as kk_sb4,
sb2_time as kk_sb2,sb3_time as kk_sb3,
case when late like '%小时_%' 
then (((left(late,2)+0)*60)+0)+(
case when left(right(late,4),2) like '时%' 
then left(right(right(late,4),3),1)
else left(right(late,4),2) end) else 
(case when late like '%分钟' then left(late,2)+0 else '' end 
)end as x,
case when ealiry like '%小时_%' 
then (((left(ealiry,2)+0)*60)+0)+(
case when left(right(ealiry,4),2) like '时%' 
then left(right(right(ealiry,4),3),1)
else left(right(ealiry,4),2) end) else 
(case when ealiry like '%分钟' then left(ealiry,2)+0 else '' end 
)end as y,
  late,ealiry
from ods_kq_yc_kk b

以上就可以将迟到时间处理正常的分钟数了