[SOLVED] Ordering text fields by letters first before numbers for all characters

Ordering text fields by letters first before numbers for all characters

I am trying to order the below text fields show they come out in ascending order with letters coming first;

B054QL9
B05KY35
B9L57F0
BBQ2T21
BYZKJ27
0043823

The current code I have is;

SELECT
    i.tclstk
FROM (
        SELECT DISTINCT
            tclstk
        FROM Figaro.dbo.tccclw WITH(NOLOCK)
        WHERE tclrqn = '6379'
            AND tclbgt = 'SPC'
            AND tclcom <> '0.00'
    ) i
ORDER BY (CASE WHEN LEFT(i.tclstk, 1) LIKE '[a-Z]' THEN 0 ELSE 1 END)
    ,i.tclstk

However what this does is it will only look at the first character and order them in alphabetical order from there, and then as per the above list it will order them numbers first for the remaining characters.

The order should be;

BBQ2T21
BYZKJ27
B05KY35
B054QL9
B9L57F0
0043823

The actual dataset being used is much larger than the above list so the above code would need to be amended to order all seven characters.

Solution

If you are using SQL SERVER 2017+, you can use this to reverse the order of numerical and letters characters.

SELECT
    i.tclstk
FROM (
        SELECT DISTINCT
            tclstk
        FROM Figaro.dbo.tccclw WITH(NOLOCK)
        WHERE tclrqn = '6379'
            AND tclbgt = 'SPC'
            AND tclcom <> '0.00'
    ) i
ORDER BY TRANSLATE(i.tclstk, 'abcdefghijklmnopqrstuvwxyz0123456789', '0123456789abcdefghijklmnopqrstuvwxyz')
    ,i.tclstk