Issues with query order

Question

I have to order the following categories like this:

1
2
3
4
5
6A
6B
6C
6D
7A
7B
10
11
12

How would I accomplish this via a SQL query?

I have tried:

Select * 
From table 
Order By len(category_name), category_name

But they order like this:

1
2
3
4
5
10
11
12
6A
6B
6C
6D
7A
7B

Any help would be greatly appreciated!

Answer 1

You can use a Scalar-Valued Function (more info about scalar-valued functions here) that removes letters from your categories' names (more information about this specific function here) and use the result to order your data:

--1. declare a new function that removes all letters from your category name
--1.1 if the function already exists delete it
if OBJECT_ID('fx_remove_letters') is not null
drop function fx_remove_letters

go
--1.2 create a function to remove all letters (CHAR(65) is letter A, char(90) is letter Z)
create function fx_remove_letters(@str NVARCHAR(max))
returns  NVARCHAR(max)
as
begin
    DECLARE @loop INT
    SET @loop = 0
    WHILE @loop < 26
        BEGIN
        SET @str = REPLACE(@str, CHAR(65 + @loop), '')
        SET @loop = @loop + 1
        END
    return @str
end
go
--2. this table variable holds your categories in a random order
declare @tmp table (category_name nvarchar(10))

--3. populate the test table variable 
insert into @tmp 
values('6D'),('2'),('3'),('12'),('5'),('6C'),('6B'),
      ('1'),('7A'),('4'),('7B'),('10'),('11'),('6A')

--4. select your data ordering them with the function we defined at the beginning
select category_name 
from @tmp 
order by cast(dbo.fx_remove_letters(category_name) as int), category_name

Here is the table before ordering:

Now the results after the order by: