I have the following input.
INPUT:
TableA
ID Sentences
--- ----------
1 I am a student
2 Have a nice time guys!
What I need to do is to extract the words from the sentence(s)
and insert each individual word in another table
OUTPUT:
SentenceID WordOccurance Word
---------- ------------ -----
1 1 I
1 2 am
1 3 a
1 4 student
2 1 Have
2 2 a
2 3 nice
2 4 time
2 5 guys!
I was able to get the answer by using the below query
;With numCTE As
(
Select rn = 1
Union all
Select rn+1 from numCTE where rn<1000)
select
SentenceID=id,
WordOccurance=row_number()over(partition by TableA.ID order by rn),
Word = substring(' '+sentences+' ', rn+1, charindex(' ',' '+sentences+' ', rn+1)-rn-1)
from
TableA
join numCTE on rn <= len(' '+sentences+' ')
where
substring(' '+sentences+' ', rn,1) = ' '
order by
id, rn
How can I improve this query of mine.?
Basically I am looking for a better solution than the one presented
Thanks
