Multi-Level searching?

[volatileboy]

Hey people just a small amount of help, im not getting errors but im really stuck with something, MYSQL version is:  MySQL client version: 5.0.51a

 

Basically I have a column in my database called article_title, I have an SQL query (below) that searches for different types of match to the input string as you can see.  The problem is that if I was to search for "ta" for example, then "stain" would be returned as a match before "tarzan".  Here is the query:

 

"SELECT * FROM info_articles WHERE article_title LIKE 'term' OR article_title LIKE '%term' OR article_title LIKE 'term%' OR article_title LIKE '%term%'"

 

Is there any way of searching it so that the results would be organised in a better way?  I have looked at various SQL websites for a way around it but I couldn't find anything.

 

Any help is greatly appreciated!

[fenway]

If you'd like to rank partial substring matches by distances from string position 0, that's not hard to do in application code.

[roopurt18]

If you want the matches from '$term%' to appear before those matched by '%$term%' there are a couple of ways you can do it.

 

SELECT * 
FROM info_articles 
WHERE 
  article_title LIKE 'term' 
  OR article_title LIKE '%term' 
  OR article_title LIKE 'term%' 
  OR article_title LIKE '%term%'

 

could become

 

SELECT *, 0 as `sort_order`
FROM info_articles 
WHERE 
  article_title LIKE 'term' 
UNION
SELECT *, 1 as `sort_order`
FROM info_articles 
WHERE 
  article_title LIKE 'term%' 
UNION
SELECT *, 2 as `sort_order`
FROM info_articles 
WHERE 
  article_title LIKE '%term%'
UNION
SELECT *, 4 as `sort_order`
FROM info_articles 
WHERE 
  article_title LIKE '%term' 
ORDER BY `sort_order`, `article_title`

 

If you don't want to use a UNION, then you might get away with some form of a CASE...WHEN in the ORDER BY clause of your original query.

[fenway]

Of course, %term% covers all 4 cases.