奇葩的sql，奇葩sql

奇葩的sql，奇葩sql

字母加数字字段（number）排序: 数字个数有限制

SELECT *,IF(SUBSTRING(number,-1) REGEXP '^[[:digit:]]$',IF(SUBSTRING(number,-2) REGEXP '^[[:digit:]]{2}$',IF(SUBSTRING(number,-3) REGEXP '^[[:digit:]]{3}$',IF(SUBSTRING(number,-4) REGEXP '^[[:digit:]]{4}$',IF(SUBSTRING(number,-5) REGEXP '^[[:digit:]]{5}$',IF(SUBSTRING(number,-6) REGEXP '^[[:digit:]]{6}$',IF(SUBSTRING(number,-7) REGEXP '^[[:digit:]]{7}$',IF(SUBSTRING(number,-8) REGEXP '^[[:digit:]]{8}$',IF(SUBSTRING(number,-9) REGEXP '^[[:digit:]]{9}$',IF(SUBSTRING(number,-10) REGEXP '^[[:digit:]]{10}$',@pos:=-10,@pos:=-9),@pos:=-8),@pos:=-7),@pos:=-6),@pos:=-5),@pos:=-4),@pos:=-3),@pos:=-2) ,@pos:=-1),@pos:=0) as tmp_pos  
,SUBSTRING(number,1,CHAR_LENGTH(number)+@pos) prefix,IF(@pos=0,0,SUBSTRING(number,@pos)+0) as num ,@pos
FROM `member_card_create`
ORDER BY `prefix` ASC, `num` ASC

正则表达式相关自定义方法

https://github.com/hholzgra/mysql-udf-regexp