mysql截取字符串函数

目标 将 rull字段值的0.1g*14 粒/1.5mg*30 片/100ml(氨甲环酸0.5g:氯化钠0.84g)*1 瓶 中的mg/g/ml开头的数字取出设置到另外一个字段上去

SELECT id  from sheet2 where rull  like '%ml%' ;

SELECT id,count, LEFT(rull,LOCATE('g',rull)-1)   from sheet2 where rull  like '%g%' ;

SELECT id,count, LEFT(rull,LOCATE('mg',rull)-1)   from sheet2 where rull  like '%mg%' and id not in (SELECT c.id from ( 

SELECT id  from sheet2 where rull  like '%ml%' ) c )

update sheet2 set count=LEFT(rull,LOCATE('mg',rull)-1)    where rull  like '%mg%'  and  id not in (SELECT c.id from ( SELECT id  from sheet2 where rull  like '%ml%' ) c);


update sheet2 set count=LEFT(rull,LOCATE('mg',rull)-1)    where rull  like '%mg%'  and  id not in (SELECT c.id from ( SELECT id  from sheet2 where rull  like '%ml%' ) c);

update sheet2 set count=LEFT(rull,LOCATE('ml',rull)-1)    where rull  like '%ml%' ;

substring_index(str,delim,count)，str是要截取的字符串，delim是截取的字段 count是从哪里开始截取(为0则是左边第0个开始，1位左边开始第一个选取左边的，-1从右边第一个开始选取右边的