mysql中字符串1.1.1/1.2.1/1.20.2/1.29.5/3.1/4.0排序问题_mysql 实现 1.1.1 排序

mysql中字符串1.1.1/1.2.1/1.20.2/1.29.5/3.1/4.0排序问题

# create by  john tang
# date 2019-07-24
# this fun is for serial_numbe as "1.1.1.1,2.0,1.32.10" to order by 
# use it  likes select * from test ORDER BY split_serial_number(serial_number,'.') ASC
 
create function split_serial_number(str varchar (1000),delimiter varchar(1)) returns varchar(200)
begin
declare cur_str varchar(200);
declare sub_len int;
declare sub_str varchar(200);
declare ret_str varchar(200);
declare loop_count int;
 
set cur_str = str;
set sub_len = -1;
set ret_str = '';
set loop_count = 0;
 
repeat
set cur_str = substring(cur_str,sub_len + 2); 
set sub_str = substring_index(cur_str,delimiter,1);
set sub_len = length(sub_str);
 
if sub_len !=0 then
set ret_str = concat(ret_str,lpad(sub_str,6,'0'));
end if;
 
set loop_count = loop_count + 1;
until sub_len = 0 
end repeat;
 
return ret_str;
end;