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
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
