Matching MySQL ORDER BY

Question

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 to get all rows, but with those rows that match a specific keyword. 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 that I would prefer to sort the corresponding lines by how close to the beginning of the field the keyword is. Since instr () returns 0 for no match, inconsistent lines appear first when I ORDER BY INSTR (name, 'tac') ASC. I could not find a way around this.

, MySQL

1
2
3
4
0
0
0

instr(), NULL 0.

Answer 1

2 , , , ( 0s )

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

NULL, , 0 .

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

(NULL)
1

Answer 2

IF , : , , :

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

Answer 3

If succination is your thing:

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