Link to home
Start Free TrialLog in
Avatar of W D
W DFlag for United States of America

asked on

Cast and substring in Order by clause

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!

Best regards,
wdelaney
Avatar of Aneesh
Aneesh
Flag of Canada image

SELECT * from Product --to play with the product number sort  
ORDER BY CAST(REPLACE(ProdNo,'-','') AS Int)
ORDER BY CASE WHEN CHARINDEX('-', ProdNo) > 0 THEN LEFT(ProdNo, CHARINDEX('-', ProdNo) - 1) ELSE ProdNo END
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W D

ASKER

aneeshattingal,
Seems to work but when upon closer inspection, the numeric sort doesn't seem to work:
6050-1                        
60561
60624
60631
6120-1                        
6250-1                        
62829
62836
62843
If at all possible, I'd like 6050-1, 6120-1 and 6250-1 to come numerically before the other numbers in this list.
I'll try BriCrowe's method...
Best regards,
wdelaney          
You were already on the right track the problem you were getting is when the ProdNo didn't contain a dash you would end up trying to take a the -1 left characters.
Avatar of W D

ASKER

BriCrowe,
Your method seems to work.
You are BRILLIANT!
Many, many thanks!