Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql - select match the most characters

I don't know what function this is called but I am trying to select a row that matches/has the most characters for the output. Here is what I have tried.

select * from table where title like '%alotofcharactershere%' limit 1

The above only works for the entire word of: alotofcharactershere

What I really want is for it to break it up: a l o t o f c h a r a c t e r s h e r e and find a row that has the most matching characters giving.

Please kindly help if you know what I'm talking about. Thank you in advance.

like image 445
thevoipman Avatar asked Aug 04 '12 15:08

thevoipman


2 Answers

I've been digging around and I'm not an SQL expert but I was thinking around the lines of ordering by char length after a replacement of chars that do not match.

Something like: SELECT * FROM your_table ORDER BY LENGTH(REGEXP_REPLACE(title, '[^abcdefg]', ''));

2 problems though. It does not match the most different chars found. So aaaa will be of higher value than abc. And I think you mean you want abc to be of higher value. 2nd problem is probably the biggest, mySQL does not support this regex. And not sure anything does. But I did find this article: https://launchpad.net/mysql-udf-regexp

Though I think there must be something much simpler and something that actually works out of the box, this answer might just trigger someones mind to think out of the box.

There also might be a solution with a lot of joins but that's most likely slower than selecting everything and sort it in php(or alike)

like image 107
René Avatar answered Sep 20 '22 23:09

René


It's not clear what you're trying to accomplish so I wonder if you have looked into Full Text Searches. The ability to find relevance might give you some ideas or alternatives.

like image 45
GDP Avatar answered Sep 21 '22 23:09

GDP