Replace beginning words(SQL SERVER 2005, SET BASED)
- by Newbie
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
