Jump to content

Sort using CASE and LEN


Recommended Posts

Hello guys, i need some experienced info here.. i need to sort selected data, thats why i used case like:

SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1WHEN CustomerName LIKE '%b%' THEN 2ELSE 3END

Everythings fine here, but I need to sort each sector (1, 2 and 3) seperately by CustomerName`s lenght. How to do that?

any ideas ?

Link to comment
Share on other sites

You can order further by LENGTH(CustomerName).

And where should i put this function ? Because i was trying to put it everywhere, still doesnt work..


Should i put it smth like:

SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1 ORDER BY LENGHT(CustomerName)WHEN CustomerName LIKE '%b%' THEN 2 ORDER BY LENGHT(CustomerName)ELSE 3 ORDER BY LENGHT(CustomerName)END
I mean, if i have data like:
and in my order (without sorting by lenght) it appears like:
apartament, yard, bell, ben, house, cup.
if i put selected data in sectors, by which case it was selected, it would look like this:
(1 order): apartament, yard, ball
(2 order): bell, ben
(3 order): house, cup
and i need to sort them by length in DESC order, so it should look like this:
(1 order): ball, yard, apartament.
(2 order): ben, bell.
(3 order): cup, house.
so all selected data should appear like:
ball, yard, apartament, ben, bell, cup, house.
how to do that with case and order by length?
Edited by LAs
Link to comment
Share on other sites



Because i was trying to put it everywhere, still doesnt work..

Well, then you obviously didn't try it everywhere. How about this:


SELECT * FROM Customers ORDER BY CASEWHEN CustomerName LIKE '%a%' THEN 1 WHEN CustomerName LIKE '%b%' THEN 2 ELSE 3END, LENGTH(CustomerName)


It's the same as "ORDER BY a, b". In your case, "a" is the entire case..end statement and "b" is the length.

Link to comment
Share on other sites

  • 4 weeks later...

Ok guys, need to wake up this thread again.


When i`m using sorting method with "order by case" and when ... then [number], i can use only 255 arguemnts in this state. The words like "when" "then" are described as variables (am i write?). Ok, so how to avoid those arguments limit ?


What kind of trick do you know and using, guys ?

Link to comment
Share on other sites

In general, those words are called keywords. That also includes things like SELECt, FROM, WHERE, etc. There are different types of keywords though. For CASE, I believe that is a statement (the entire CASE..END block would be referred to as a case statement). Another generic term is expression, an expression is anything that will evaluate to a value. So the case statement is also an expression since it will evaluate to a number.


This is the manual for the case statement:




Look at the note on the top of that page. It says that there is both a case statement and case expression. It sounds like the case statement that they show is for stored procedures, and the case expression is for regular queries. It looks like the major difference is that the case statement ends with END CASE and the expression ends with END. This is the manual page for the case expression:




They also refer to it as the case operator, but that's a pretty loose definition of an operator. Other operators are things like +, -, AND, OR, etc.


Inside the case, the WHEN..THEN would probably be referred to as a when clause or when expression.


There's some discussion about that issue here:




It looks like there a limit of 255 expressions. If you need more than that then you can nest the case statement (i.e. make the else another case statement that lists more options), or that page suggests storing the values in a temporary table instead of using a case.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...