• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

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
0
wdelaney05
Asked:
wdelaney05
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * from Product --to play with the product number sort  
ORDER BY CAST(REPLACE(ProdNo,'-','') AS Int)
0
 
Brian CroweCommented:
ORDER BY CASE WHEN CHARINDEX('-', ProdNo) > 0 THEN LEFT(ProdNo, CHARINDEX('-', ProdNo) - 1) ELSE ProdNo END
0
 
Brian CroweCommented:
correction...forgot to cast to int

ORDER BY CAST(CASE WHEN CHARINDEX('-', ProdNo) > 0 THEN LEFT(ProdNo, CHARINDEX('-', ProdNo) - 1) ELSE ProdNo END as int)

with the replace method 10032 would be listed before 10030-1 because it would be converted to 100301
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wdelaney05Author Commented:
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          
0
 
Brian CroweCommented:
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.
0
 
wdelaney05Author Commented:
BriCrowe,
Your method seems to work.
You are BRILLIANT!
Many, many thanks!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now