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