7

I am trying to order my mysql queries based on the first letter but every method I have used is restricting my search.

Example MySQL table Value computer services abc computer services dynamic computer services

If I search for computer services I want the results to returned as:

**Name**
computer services
abc computer services
dynamic computer services

I am using mysql fullsearch text but if i use name LIKE 'c%' I don't the other two results e.g.

SELECT name FROM table WHERE match('name') against('computer services*' IN BOOLEAN MODE) AND name LIKE 'c%';

This would only return

  • computer services

But I want it to return:

  • computer services
  • abc computer services
  • dynamic computer services

I am new to mysql full search text.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
James Bell
  • 604
  • 1
  • 7
  • 18
  • I don't know much about full text search (which is why I'm posting as a comment, not an answer) but I will guess try to get rid of `name LIKE 'c%'` and see what happens. This expression will only match things that begin with `c` and are followed by anything. `abc computer services` does not begin with `c` so I think that is why it doesn't get selected. – FrustratedWithFormsDesigner Feb 08 '11 at 16:32
  • Thank you to everyone who posted. Amazing respone within minutes!!! – James Bell Feb 08 '11 at 16:45

1 Answers1

2

Use an order by clause that matches the 'starts with' case first. I'm using not like here because the boolean returns 0 or 1 and we want to reverse that to match the starts with case first.

SELECT name 
FROM table 
WHERE match('name') against('computer services*' IN BOOLEAN MODE)
ORDER BY name NOT LIKE 'computer services%', name;
a'r
  • 35,921
  • 7
  • 66
  • 67