Solved

MySQL similar text feature?

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 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