Solved

MySQL similar text feature?

Posted on 2004-08-07
4
5,972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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