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

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?

CodePudding user response：

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