How to avoid cursor in sql server? also want to avoid while loop

Question

Hi All,

I have two tables in my database, 1st one contains a couple of sentences, like 'I like apples and bananas', 2nd one contains key word, like 'apple' & 'orange'. I want to create a sql statements or usp to list all the colomns in the 1st table with the keywords in the 2nd one. How can I achieve that without using cursors?

Would anyone have some smart ideas rather than while loop? That'll be great.

Thanks, Ling

Answer 1

See if that's what you need...

Option 1 would return a row for each keyword matching the phrase...

Option 2 returns a CSV of the keywords that are in the phrase. Note that this uses SQL 2005 related functions.

I've made a test case for you... Perhaps would help your explanation of the problem.

create table #test(id int identity(1,1), phrase varchar(1000))

create table #kenter code hereeyword(id int identity(1,1), keyword varchar(50))

insert into #test
select 'I like apples and bananas'

insert into #keyword
select 'APPLE'
UNION
select 'BANANA'
UNION 
select 'RASPBERY'


select 
    t.*,
    k.keyword
 from #test t
    inner join #keyword k on t.phrase like '%' + k.keyword + '%'

--OR...


select 
    t.*,
    Keywords = (    
        select k.keyword + ',' as [text()] 
        from #keyword k 
        where t.phrase like '%' + k.keyword + '%'
        FOR XML PATH('')) 
 from #test t



drop table #test

drop table #keyword