Define a custom ORDER BY order in mySQL using the LIKE word

How can I make a custom order where I sort the rows by NAME where the first rows’ name has Bob in it followed by rows with name of Alex in it?
To explain what exactly I mean: I have made the following query to sort result if NAME = ‘Bob’ and if NAME = ‘Alex’:

SELECT * FROM table
ORDER BY CASE `NAME`
         WHEN 'Bob' THEN 1
         WHEN 'Alex' THEN 2
         ELSE 3
         END

But this only works when the NAME is exactly equal to Bob or Alex. I want to modify it to sort if the NAME has Bob or Alex in it, essentially if NAME LIKE '%Bob%' and NAME LIKE '%Alex%'. I tried something like the following but it does not work.

ORDER BY CASE `NAME`
         WHEN LIKE '%Bob%' THEN 1
         WHEN LIKE '%Alex%' THEN 2
         ELSE 3
         END

What is the correct syntax for this?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use the other form of CASE where you specify a condition in WHEN rather than a value.

ORDER BY CASE
         WHEN NAME LIKE '%Bob%' THEN 1
         WHEN NAME LIKE '%Alex%' THEN 2
         ELSE 3
         END

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating