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.

Answer 1

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)