MySQL -- Order by SubString matching Index

Question

I have a table which has companies (id, name). Before I had to search the input query against records which was handled with "like". Sometime client wanted it to end with the input query sometime it could occur in between sometime it should start with it. So I used different variation of "like"

Input Query: Micro

select * from Company where CompanyName like "Micro%";
select * from Company where CompanyName like "%Micro%";
select * from Company where CompanyName like "%Micro";

Pretty simple right. Now the variation started to come which were not that easy to handle with "like". Like one case occurred to search the query in starting of the words. Like for same above input query the result set should contain

Microcompany Something
Something Somethin Microcompany
Something Microcompany

So i switched to using Regular Expressions

Now my Query turned into

select * from company where CompanyName regexp '[[:<:]]Micro';

Question: Now I want the result sorted on the closest matching index. I mean the one on which match occurred on index closer to 0 will come first and then the others. For the above search result it should be order as follow.

Microcompany Something
Something Microcompany
Something Somethin Microcompany

Is it achievable through MySQL. If yes than how? and what are the alternatives?

ORDER BY LOCATE('Micro', CompanyName)? Though I'd suggest that you also look into fulltext search, or else an engine like Apache Solr or Sphinx, since your current pattern matching searches aren't sargable. — eggyal
– eggyal, Commented Mar 25, 2015 at 10:01
Yeah I have set up Apache Solr because beside this case there is a lot of search which is getting more and more complicated by day. But for the time being i think ORDER BY LOCATE('Micro', CompanyName) will work. Thanks — planet260
– planet260, Commented Mar 25, 2015 at 10:19

Gordon Linoff · Accepted Answer · 2015-03-25 10:39:17Z

1

Although there may be exceptions where the following doesn't work, it is probably good enough for most cases:

select *
from company
where CompanyName regexp '[[:<:]]Micro'
order by instr( CompanyName, 'Micro');

That is, only match names where the "Micro" starts a word in the name. Then return them by the order of how close "Micro" is to the front of the word. I doubt there are many names that have "Micro" twice in the name -- and if they do, there is a good chance that each occurrence starts a word anyway.

answered Mar 25, 2015 at 10:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

planet260 Over a year ago

How about ORDER BY LOCATE('Micro', CompanyName). Isn't it the same?

Gordon Linoff Over a year ago

@planet260 . . . Should be, and there is also the function position().

Thaylon · Accepted Answer · 2015-03-25 10:15:14Z

0

select * from company where instr( CompanyName, 'Micro' ) > 0 order by instr( CompanyName, 'Micro' );

Exclude entries that dont contain the search phrase (index == 0). Order by character index of the first match.

answered Mar 25, 2015 at 10:15

Thaylon

4535 silver badges12 bronze badges

1 Comment

planet260 Over a year ago

The search case is query in starting of the words.

Collectives™ on Stack Overflow

MySQL -- Order by SubString matching Index

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related