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.

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

This is far from pretty, and honestly, I would suggest that perhaps you need to reconsider your data, but it "does the job".

SELECT *
FROM (VALUES('B054QL9'),
            ('B05KY35'),
            ('B9L57F0'),
            ('BBQ2T21'),
            ('BYZKJ27'),
            ('0043823'))V(YourString)
ORDER BY CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,1,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,1,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,2,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,2,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,3,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,3,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,4,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,4,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,5,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,5,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,6,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,6,1),
         CASE WHEN TRY_CONVERT(int,SUBSTRING(V.YourString,7,1)) IS NULL THEN 0 ELSE 1 END,
         SUBSTRING(V.YourString,7,1);

My version:

select *
from (
	VALUES	(N'BBQ2T21')
	,	(N'BYZKJ27')
	,	(N'B05KY35')
	,	(N'B054QL9')
	,	(N'B9L57F0')
	,	(N'0043823')
) t (col1)
order by replace(replace(replace(replace(replace(replace(replace(replace(REPLACE(replace(cast(col1 as nvarchar(100)), N'0', nchar(64000)), N'1', nchar(64001)), N'2', nchar(64003)), N'3', nchar(64003)), N'4', nchar(64004)), N'5', nchar(64005)), N'6', nchar(64006)), N'7', nchar(64007)), N'8', nchar(64008)), N'9', nchar(64009))

What it does is to move the numbers to high plane unicode characters which should sort above the regular latin ones.

I wouldn't try this at home though, and i think you should avoid this kind of sorting if possible