Sorting data by the word contained in the string

Question

I have a tablix form that has a list of equipment whose names contain a specific prefix. There are, for example, chlorine filters from 1 to 10 with name like 'Filter #1 cl., Filter #2 cl. and there are cement filters from 1 to 8 with names like 'Filter #1 cmt' etc.

I need the equipment list to be sorted by equipment type from Z to A, like:

Filter #1 cl
Filter #2 cl
Filter #1 cmt
Filter #2 cmt

I tried to sort by description, but the equipment is displayed randomly like

Filter #1 cmt
Filter #1 cl
Filter #2 cl
Filter #2 cmt

Answer 1

If your strings always have the syntax you have shown, you need to sort twice.

With first priority by the substring after the last space, with second priority by the substring before that space.

So this will do for your sample input:

SELECT yourcolumn
FROM yourtable
ORDER BY RIGHT(yourcolumn, (CHARINDEX(' ',REVERSE(yourcolumn),0))),
SUBSTRING(yourcolumn,0, LEN(yourcolumn) - LEN
(RIGHT(yourcolumn, Charindex(' ', Reverse(yourcolumn)) - 1)));

Try out here: db<>fiddle