sql - MySQL ORDER BY keyword match -

i have table this:

 mysql> select * test; +-------------+ | name        | +-------------+ | 1         | | 2         | | 3       | | tic tac toe | | tac toe tic | +-------------+ 5 rows in set (0.00 sec) 

i query rows rows matching keyword first. got far:

 mysql> select * test order instr(name, 'tac') desc; +-------------+ | name        | +-------------+ | tic tac toe | | tac toe tic | | 1         | | 2         | | 3       | +-------------+ 5 rows in set (0.01 sec) 

the problem prefer order matching rows how close beginning of field keyword occurs. since instr() returns 0 no match, non-matching rows come first when order instr(name, 'tac') asc. haven't been able figure out way around this.

i need mysql order this

1 2 3 4 0 0 0 

or need instr() return null instead of 0.

you need order 2 columns, first 1 indicate whether match made (to make 0s go bottom)

select * test order     case when instr(name, 'tac') = 0 1 else 0 end,     instr(name, 'tac') desc; 

a note on using null, come top of query, won't work convert 0 null.

select (select 1 union select null) b order 

result

(null) 1