[親]と[子]の二列(二階層)になっていて、それぞれ文字列型なのだが
場合によっては[子]が数値要素だけの場合、
数値としてソートをかけたいする(そうしないと「10」が「2」の前に来て格好悪い)

というこで、出し分けるSQL文は以下の通り
┌──────────────────────────────────────┐
│SELECT [主表].[親],                                                         │
│       [主表].[子],                                                         │
│    FROM [表] AS [主表]                                                     │
│    ORDER BY [主表].[親] ASC,                                               │
│             CASE                                                           │
│               WHEN EXISTS (                                                │
│                        SELECT *                                            │
│                            FROM [表] AS [調査表]                           │
│                            WHERE [調査表].[親] = [A].[親]                  │
│                              AND (ISNUMERIC([調査表].[子]) = 0             │
│                                OR ([調査表].[子] LIKE '0%'                 │
│                                AND [調査表].[子] <> '0'))                  │
│                    ) THEN                                                  │
│                 [主表].[子]                                                │
│               ELSE                                                         │
│                 FORMAT(CAST([主表].[子] AS int), '00000000')               │
│             END ASC;                                                       │
└──────────────────────────────────────┘
「0001」とかについては文字列として並び替えるおまけつき(しかも「0」だけは除く)