Trying to compare two tables with SOUNDEX

Question

Could someone please explain how to use SOUNDEX (mysql/php) to match multiple words? I'm trying to make a simple in-site ad system.

I have one table with columns "ad_id" and "keywords", and another table with "page_url" and "keywords". The problem is, the first table, all the keywords of a given ad_id are in one row, but with the second, there are multiple rows like this:

page_url:-----keywords:
page1.php-----keyword1, keyword2, keyword3
page1.php-----keyword4
page2.php-----anotherkeyword

I'm trying to compare the two tables to figure out which ad should be shown. I'm having some really problems trying to get it to understand there are multiple rows that correspond to the same page name on the second table, let alone the fact that they're all separated by commas!

Any idea where to start? Any advice would be appreciated. Thanks.

Normalize the table: move keywords to a separate table and join them on the pages table by their PK. — Wrikken, Jul 12 '11 at 21:58
ok, say i have all of the keywords for each page_url stored in one cell, all seperated by commas, like: — Test Tester, Jul 12 '11 at 22:00

score 2 · Answer 1 · answered Jul 12 '11 at 21:58

2

You need to create another table from the second table with only one keyword for each row. Then matching the two tables is easy.

answered Jul 12 '11 at 21:58

antlersoft

14,636
4
35
55

Ovais Khatri · Accepted Answer · 2011-07-12T23:54:38.483

create this split function in sql server:

   CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

it can be used as: select * from dbo.split('val1,val2,val3',',') it will return result in table form...

val1
val2
val3

Then use this function:

declare @tmp table(keyword varchar(100))
insert into @tmp
select tbl.item from 
(select split(t2.keyword) as item from table2) as tbl
Select * from table1 t1
where t1.keyword in (select keyword from @tmp)

Have you ever tried your answer, just for fun? SOUNDEX does not work on comma-separated strings, or at least, it doesn't do what you think it does. Just check with t1.keyword='foo' and t2.keyword='bar, foo, something'. — Wrikken, Jul 12 '11 at 22:15

Trying to compare two tables with SOUNDEX

2 Answers2