CODEWITHSUNDEEP
sqlmysql
Max Kielland
Max Kielland

Reputation: 5841

MySQL, Finding words from one string in another string

I have no idea if this is possible to do in one SQL statement. I have been reading all day and can't seem to find a solution and to be frank, my experience is only at the tip of the iceberg when it comes to SQL.

I have two different tables, lets say: items and customers. Both tables have a field (Notes) of type TEXT containing words separated with spaces and CR/LF pairs.

I need to select all rows (items.*) where any of the words in customers.note matches any word in items.note

Example:

Table items
ID NOTES
-- ----------
1  bag candle fork
2  max kielland
3  plate knife fork
4  wheel brownbag

Table customers
ID NOTES
-- ----------
1  brown candle fork

The result I want would be:

Items.ID Items.Note
-------- ----------
1        bag candle fork
3        plate knife fork

I have been looking at IN and LIKE (as separate solutions, not together as a pair) but they don't seem to quite solve the problem. If it is to any help, I can make sure all spaces are replaced by ','

In the long end it would be even more perfect if the compare could be made as the LIKE %..% producing a result like this:

Items.ID Items.Note
-------- ----------
1        bag candle fork
3        plate knife fork
4        wheel brownbag

as you can see the brown get a hit on brownbag.

As I said I don't know if this is possible in MySQL without splitting up the query in several questions with some PHP processing.

Thank you.

Update

With the help of OMG Ponies MATCH..AGAINST suggestion and some PHP I solved it.

$SQLSet = mysql_query("
  SELECT DISTINCT i.*
  FROM items i
  JOIN wanted w ON MATCH(i.notes)
  AGAINST(\"".$SQLRowWanted['Notes']." +'".$SQLRowWanted['Name']."'\")
");

This is running inside a loop with $SQLRowWanted from an earlier call. By encapsulating the Name column in '...' and adding a + before it, I get a higher match rate on the full name as well.

Upvotes: 3

Views: 2086

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332791

I would look at using Full Text Search (FTS). MySQL has native FTS functionality using the MATCH ... AGAINST function, but it requires that a full text index exist on the column(s). MySQL's Full Text index can only be applied to MyISAM tables, for CHAR, VARCHAR, or TEXT columns. 

SELECT i.id,
       i.notes
  FROM ITEMS i
  JOIN CUSTOMERS c ON MATCH(i.notes) AGAINST (c.notes IN BOOLEAN MODE)

Otherwise, you'll need to look at 3rd party FTS:

  • Sphinx
  • Solr (lucene based)

Upvotes: 3

FlightOfStairs
FlightOfStairs

Reputation: 133

Your table is not normalised, which makes it harder. For each of "1: bag candle fork" you should have a seperate row:

notesID | noteText
1         bag
1         candle
1         fork
2         max
2         kielland
...

Then you can simply do

SELECT * FROM customers, items WHERE customers.noteText = items.noteText;

You may need to create seperate tables with just the two columns shown above, and join them back to your customers and items table to look up more data.

Upvotes: 1

Related Questions