Cast and substring in Order by clause
Posted on 2006-05-23
Apologies in advance for my inexperience.
I have a SQL Product table with char product numbers such as '10030' , '10030-1', '9230', '910', etc.
I need to SELECT all products and have them listed in a report. Here's the kicker, though: I need to have the list sorted numerically. Is it possible to have an ORDER BY clause with a CAST and some sort of SUBSTRING statement to have the
product numbers sorted numerically. I.e., '910' would be listed first, then '9230', then '10030', then '10030-1'.
I’m using the basic SELECT * from Product to play with the product number sort and then I have:
order byorder by Cast(left(ProdNo, charindex('-',ProdNo)- 1) as integer)
but I keep getting a “Invalid length parameter passed to the substring function” syntax error msg probably because of items that don't have the dash in them. I wonder if I can use a CASE within that statement somehow?
Any assistance would be greatly appreciated!