Sorting data by the word contained in the string-CodePudding

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

CodePudding user response：

The tactic is to extract the filter name into its own column and sort on that. Working with strings in SQL gets messy really fast.

Below I find how many characters from the end of the string the last space is, by counting how many characters from the left of the reversed string the first space is. I then ask for that number of characters to the right of the actual string.

SELECT
  RIGHT(filter_column, Charindex(' ', Reverse(filter_column)) - 1) AS filter_name
FROM filters_table

CodePudding user response：

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