I am interested in ordering a cursor in a very particular way. In my program, the user can enter a search query. I would like to return the results sorted with items starting with the query they typed in, followed by the rest of the items that simple contain the query.
For example: The user types in "de". I would like my cursor to be ordered in such a way where all of the columns that start with "de" are first, followed by the rest of the columns that contain "de".
Here is what I would like my cursor to look like:
Deathless Behemoth
Deathmark
Deathmark Prelate
Deathmask Nezumi
Deathmist Raptor
Deathpact Angel
Acid Web Spider
Careful Consideration
Hell-Bent Raider
Jhovall Rider
Living Death
The cursor starting with "de" is above everything else that simply contains "de".
PS I came across some answers on this site where people used the ORDER BY FIELD() function. That sort of looks like what I'd want to use, but I get an error
Error while executing SQL query on database 'database_name': no such function: FIELD
You can put all sorts of expressions in an ORDER BY clause and coming up with an expression that says "put things that start with 'de' first" is pretty straight forward using a form of CASE:
order by case when lower(user_type) like 'de%' then 0 else 1 end,
lower(user_type)
So when the user_type column starts with 'de', the case expression evaluates to 0, otherwise it evaluates to 1 and since 0 sorts before 1, the types that begin with 'de' will come first.
The field function is, AFAIK, specific to MySQL.