MySQL ORDER BY keyword match

I have a table like this:

mysql> select * from test;
+-------------+
| name        |
+-------------+
| one         |
| two         |
| three       |
| tic tac toe |
| tac toe tic |
+-------------+
5 rows in set (0.00 sec)

I would like to query it so that I get all the rows but with those rows matching a certain keyword first. This is what I got so far:

mysql> select * from test order by instr(name, 'tac') desc;
+-------------+
| name        |
+-------------+
| tic tac toe |
| tac toe tic |
| one         |
| two         |
| three       |
+-------------+
5 rows in set (0.01 sec)

The only problem with this is I would prefer to order the matching rows by how close to the beginning of the field the keyword occurs. Since instr() returns 0 for no match, non-matching rows come up first when I ORDER BY INSTR(name, 'tac') ASC. I haven't been able to figure out a way aroun开发者_高级运维d this.

I need MySQL to order like this

1
2
3
4
0
0
0

or I need instr() to return NULL instead of 0.

You need to order by 2 columns, the first one to indicate whether a match was made (to make 0s go to bottom)

select *
from test
order by
    CASE WHEN instr(name, 'tac') = 0 then 1 else 0 end,
    instr(name, 'tac') desc;

A note on using NULL, they come to the top of the query, so it won't work for you to convert 0 to null.

select a
from (select 1 as a union all select null) b
order by a

Result

(NULL)
1

With IF you will be able to do what you want: returning a value different than zero when there is no match:

select * from test order by IF(instr(name, 'toc'), instr(name, 'toc'), 65535) desc;

If succintness is your thing:

select *
from test
order by
    instr(name, 'tac') = 0,
    instr(name, 'tac') desc;

ORDER BY FIELD(column_name, 'keyword_3', 'keyword_2', 'keyword_1', 'keyword_4')

The result will be

keyword_3,keyword_2,keyword_1,keyword_4