?
Solved

MySQL similar text feature?

Posted on 2004-08-07
4
Medium Priority
?
5,986 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 1000 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 1000 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

864 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