W D
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
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
ORDER BY CASE WHEN CHARINDEX('-', ProdNo) > 0 THEN LEFT(ProdNo, CHARINDEX('-', ProdNo) - 1) ELSE ProdNo END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
ASKER
BriCrowe,
Your method seems to work.
You are BRILLIANT!
Many, many thanks!
Your method seems to work.
You are BRILLIANT!
Many, many thanks!
ORDER BY CAST(REPLACE(ProdNo,'-',''