Solved

MySQL similar text feature?

Posted on 2004-08-07
4
5,965 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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