replace , substring,substring_index用法实例
源数据 mobile 123342,1232314,1243123,
replace(
substring(
substring_index(mobile,','a.id),
char_length(
substring_index(mobile,',',a.id-1)
)+1
),
',',
'')
a.id=2的话,substring_index(mobile,’,’a.id),提取出来的字符串便是第二个逗号前所有的字符,也就是两个电话号码。例如‘123342,1232314’
所以substring_index(mobile,’,’,a.id-1)提取出的字符串第一个逗号前的字符:便是第一个电话号码‘123342’
char_length(
substring_index(mobile,’,’,a.id-1)
)+1
所以这个长度便是7,
substring(
这个意思就是
substring_index(mobile,','a.id),
char_length(
substring_index(mobile,',',a.id-1)
)+1
),
substring('123342,1232314',7)
从第七个开始提取 返回的也就是
‘,1232314’
然后 replace就是把‘,’,全部删除掉。
便返回第二个电话号码。
–https://www.imooc.com/video/8275 mysql行转列慕课网视频,代码如下
select user_name,replace(substring(substring_index(mobile,','a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile from tb_sequence--序列表 as a
cross join(
select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile,',',''))+1 as size from user1 as b) as b
on a.id<=b.size