MYSQL数据库字母数字混合字段排序
MYSQL数据库字母数字混合字段排序
·
MYSQL数据库字母数字混合字段排序
需求 mysql 同个字段中先按字母排序,再按数字排序
表的数据
select * from test;
id | code |
---|---|
1 | AM1 |
2 | A10 |
3 | A07 |
4 | B01 |
5 | B10 |
6 | A0009 |
7 | 00010 |
8 | 00013 |
9 | 8 |
10 | 12 |
常规的写法按编码排序查出来的结果达不到想要的
select * from test ORDER BY
code
;
id | code |
---|---|
7 | 00010 |
8 | 00013 |
10 | 12 |
9 | 8 |
6 | A0009 |
3 | A07 |
2 | A10 |
1 | AM1 |
4 | B01 |
5 | B10 |
修改后
drop function if exists f_fm_extract_number;
delimiter ;;
create function f_fm_extract_number(
in_string VARCHAR(50)
)
returns varchar(50)
deterministic
begin
/*------------------------------------------------------------------------------
功能描述:数字截取
详细描述:
参数说明:
in_string VARCHAR(50) -- 需要进行数字截取的参数名称
创建时间: 2023-05-17
最后修改时间:2023-05-17
修改说明:
调用例子:
select *,
REPLACE(`code`, f_fm_extract_number(`code`), '') as strpart,
CAST(f_fm_extract_number(`code`) AS UNSIGNED) as numpart
from test
ORDER BY strpart, numpart;
------------------------------------------------------------------------------*/
declare ctrNumber VARCHAR(50);
declare finNumber VARCHAR(50) DEFAULT '';
declare sChar VARCHAR(1);
declare inti INTEGER DEFAULT 1;
if(LENGTH(in_string) > 0) then
while(inti <= LENGTH(in_string)) do
set sChar = SUBSTRING(in_string, inti, 1);
set ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9');
if (ctrNumber > 0) then
set finNumber = CONCAT(finNumber, sChar);
end if;
set inti = inti + 1;
end while;
return finNumber;
else
return '';
end if;
end;;
delimiter ;
select *,
REPLACE(code
, f_fm_extract_number(code
), ‘’) as strpart,
CAST(f_fm_extract_number(code
) AS UNSIGNED) as numpart
from test
ORDER BY strpart, numpart;
id | code | strpart | numpart |
---|---|---|---|
9 | 8 | 8 | |
7 | 00010 | 10 | |
10 | 12 | 12 | |
8 | 00013 | 13 | |
3 | A07 | A | 7 |
6 | A0009 | A | 9 |
2 | A10 | A | 10 |
1 | AM1 | AM | 1 |
4 | B01 | B | 1 |
5 | B10 | B | 10 |
更多推荐
已为社区贡献1条内容
所有评论(0)