Replace beginning words

Question

I have the below tables.

tblInput

Id  WordPosition    Words
--  -----------     -----
1   1               Hi
1   2               How
1   3               are
1   4               you
2   1               Ok
2   2               This
2   3               is
2   4               me

tblReplacement

Id  ReplacementWords
--- ----------------
1   Hi
2   are
3   Ok
4   This

The tblInput holds the list of words while the tblReplacement hold the words that we need to search in the tblInput and if a match is found then we need to replace those.

But the problem is that, we need to replace those words if any match is found at the beginning.

i.e. in the tblInput,

in case of ID 1, the words that will be replaced is only 'Hi' and not 'are'

since before 'are', 'How' is there and it is not in the tblReplacement list.

in case of Id 2, the words that will be replaced are 'Ok' & 'This'. Since these both

words are present in the tblReplacement table and after the first word i.e. 'Ok' is

replaced, the second word which is 'This' here comes first in the list of

ID category 2

. Since it is available in the tblReplacement, and is the first word now, so this will

also be replaced.

So the desired output will be

Id  NewWordsAfterReplacement
--- ------------------------
1   How
1   are
1   you 
2   is
2   me

My approach so far:

;With Cte1 As(
Select 
    t1.Id
    ,t1.Words
    ,t2.ReplacementWords
From tblInput t1 
Cross Join tblReplacement t2)
,Cte2 As(
Select Id, NewWordsAfterReplacement = REPLACE(Words,ReplacementWords,'')
From Cte1)

Select * from Cte2 where NewWordsAfterReplacement <> ''

But I am not getting the desired output. It is replacing all the matching words.

Urgent help needed*.( SET BASED )*

I am using SQL Server 2005.

Thanks

Answer 1

Hi there I couldnt exactly understand your requirement.But from what I understand this works for the given data

with cte1 as(
select 1 as id,   1 as wp, 'Hi' as words union all
select 1,  2       ,        'How' union all
select 1 ,  3   ,            'are' union all
select 1 ,  4   ,            'you' union  all
select 2 ,  1       ,        'Ok' union all
select 2 ,  2 ,              'This' union all
select  2 ,  3   ,            'is' union all
select 2 ,  4       ,        'me'
),
 cte2 as(
select 1 as id,  'Hi' as rep union all
select 2 ,  'are' union all
select 3 ,  'Ok' union all
select 4  , 'This')

select ID,words from cte1
except
select ID,words from(
select a.id,a.words,a.wp,RANK() over(partition by a.id order by wp) as rnk
from cte1 as a inner join
cte2 as b on a.words=b.rep
) as x
where x.wp=x.rnk