Solved

MySQL similar text feature?

Posted on 2004-08-07
4
5,954 Views
Last Modified: 2010-10-05
Is there a way that I can tell MySQL to select all records of which have a value in a column (BLOB) similar to 90% or some arbritary value? Example:

SELECT * from table where name LIKE "%harold%"

Then if that returns no results, is there a query which I can build again in SQL which will match similar text to some percentage? I am thinking of a function similar to:

http://us2.php.net/manual/en/function.similar-text.php

--OR--

If the above is not doable, can you say what an alternative solution might be to finding "similar" or closely matched records?

Thanks in advance.
0
Comment
Question by:drakkarnoir
4 Comments
 
LVL 32

Assisted Solution

by:ldbkutty
ldbkutty earned 250 total points
ID: 11744504
As far as i know, you cannot give a direct query like that. Even FULL TEXT search cannot give what you want. One alternative i think is like this. I am sorry if it sounds stupidly. :-D

<php

$search_string = "harold";
$temp = "yes";

while($temp == "yes")
{

$result = mysql_query("SELECT * FROM tablename WHERE name LIKE '%".$search_string."%'") or die("SQL Error: ".mysql_error());
$total = mysql_num_rows($result);

if( $total > 0 || strlen($search_string) < 4 )  // One or More Rows exist (You can also make it as ATLEAST, say 5 Rows to be returned)
{
   $temp = "no";
}
else
{
  $search_string = substr($search_string, 0, strlen($search_string)-1);
}

}

?>

There might be better idea from other experts.
0
 
LVL 9

Accepted Solution

by:
e-tsik earned 250 total points
ID: 11745612
Hi :-)

I notices that you're looking up a name.
With names, it is possible to use the soundex function:
SELECT * from table where (name LIKE "%harold%") or  (soundex(name) LIKE soundex("harold") )

You'll probaby notice that I still recommend using the like match just to be sure.

More information on Soundex:
http://www.archives.gov/research_room/genealogy/census/soundex.html

MySQL String functions reference:
http://dev.mysql.com/doc/mysql/en/String_functions.html

Enjoy...
0
 
LVL 11

Expert Comment

by:ajaikumarr
ID: 11750069
Hai,

As for as i understand you can't implement soundex using full text search or achive it fully using the query alone.. so you must write some code to search string by string on a text field to achive this. (http://www.bitmechanic.com/mail-archives/mysql/current/1525.html)

Or you can get some search engine softwares like the below.
http://www.searchtools.com/tools/nextrieve.html

Hope it helps you.
Bye
Ajai
0
 

Author Comment

by:drakkarnoir
ID: 11754401
Thanks!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now