SQL Ordering by a substring with a condition

Question

I have data that looks like this:

[Model Number]
[Model Number]*1
[Model Number]*4
[Model Number]*10
[Model Number]*13

And when I select them I would liek to order them by the number after the " * ", I have it almost working but I dont know how to deal with the case where I dont have a " * ". Here is the last part of my query:

ORDER BY 
CAST(SUBSTRING(COL_NAME, CHARINDEX('*', COL_NAME) + 1, LEN(COL_NAME)) AS INT) DESC

Thanks for the help

Answer 1

A couple of solutions that should get you what you're after:

ORDER BY TRY_CONVERT(int,RIGHT(COL_NAME, NULLIF(CHARINDEX('*',REVERSE(COL_NAME)),0) -1)) DESC;

ORDER BY TRY_CONVERT(int, STUFF(COL_NAME, 1, NULLIF(CHARINDEX('*', COL_NAME),0),'')) DESC;

The NULLIF resolves the issue if there is no '*', as CHARINDEX will return 0. Then you don't end up passing an invalid (negative) value to the RIGHT function, as NULL - 1 = NULL.

My personal preference would be using STUFF, as REVERSE is quite an expensive function.