MYSQL : 文字列型カラムのOrder 数値が入っているものに関して

Question

やりたいこと

今回行いことは文字列型カラムの並び替えです。
数値型のカラムに以下のようなデータが入っています。

# code
3B
12D
XXX
0A
1C

このデータに以下のようなソートをかけたいです。

# code
0A
1C
3B
12D
XXX

現状

このような形で数値から始まるものと文字列のものを分けて、数値から始まるものは数値型にcastしてorder
文字から始まるものは文字でorder、その後結合をおこなっています。

"(SELECT * FROM table
WHERE `code` REGEXP '^[0-9]' 
ORDER BY cast(`code` as UNSIGNED) ,`code`)
UNION
(SELECT * FROM table
WHERE `code` NOT REGEXP '^[0-9]' 
ORDER BY `code`)"

問題点

UNION後にUNION前のorderが保証されないようでして、順番が結局ぐちゃぐちゃにいなってしまうことがあります。
アプリケーション側でそれぞれSQLを打って結合する方法はありますが、SQL1回で綺麗に行える方法があればご教授願いたいです。

追記

検証中はMysql8で行っていたので、ベストアンサーのもので問題なく動きましたが、本番環境がMysql5.7でWindow関数(ROW_NUMBER)が動かなかったため、もう片方の回答で行いました。

結論のコード残しときます。

SELECT *, 
CASE WHEN code REGEXP '^[0-9]' 
    THEN cast(code AS UNSIGNED) 
    ELSE 9999 
END reg 
FROM table
ORDER BY reg, code"

Answer 1

たとえば以下のようなクエリになるかと思います。

SELECT *
FROM (
    SELECT *, 0 AS v1, ROW_NUMBER() OVER (ORDER BY CAST(code AS UNSIGNED)) AS v2
    FROM T 
    WHERE code REGEXP '^[0-9]'
  UNION ALL 
    SELECT *, 1 AS v1, ROW_NUMBER() OVER (ORDER BY code) AS v2
    FROM T 
    WHERE code NOT REGEXP '^[0-9]'
) X
ORDER BY v1, v2

Comments

[muteki_gamer]

union前の順番を保持しておき、もう一度orderするという方法は回答をいただいてからハッとさせられました。
回答ありがとうございます。

Answer 2

文字列には適当に大きな数字をふっておくとか？

create table tbl(id int primary key,code varchar(10));
insert into tbl values
(1,'3B'),
(2,'12D'),
(3,'XXX'),
(4,'0A'),
(5,'1C');

select id,code,case when code regexp '^\\d' then cast(code as INTEGER) else 9999 end reg from tbl
order by reg;

Comments

[muteki_gamer]

こちらの方法でも実装できそうです。
SQLにIF文をあまり仕込みたくないという気持ちがあるため、今回はもう一方の回答をベストアンサーとさせていただきました。
ありがとうございました！