Question asked by a user of SQL
& MSBI Groups-I have a paragraph, I have taken
paragraph as a string in sql then I want how many words are repeated, and how
many times the words are repeated?
Eg-“Ram
name is Ram because of lord Ram since Ram Father is devoted to lord Ram.”
Output should be words_Repeated
Word_Count as below
Solution-I have written the below code
that will count each alphabet in the string.Secondly it will also handle the comma and full Stop in the word for eg if a word contains comma or full stop it will remove the (Comma & full stop) and count it as a word as example below.
declare @str varchar(200) = 'Ram name is Ram because of
lord Ram, since Ram Father is devoted to lord Ram.'
declare @temptable table(word varchar(10), word_count int, length int)
declare @value varchar(10)
declare @index int
while (LEN(@str)
> 0)
begin
set @index = CHARINDEX(' ',@str,1)
if (@index = 0)
begin
set @value =Replace(@str,'.','')
if exists (select * from @temptable where word=@value)
update @temptable set word_count=word_count+1 where word=@value
else
insert into @temptable select @value,1,LEN(@value)
break;
end
else
set @value = LEFT(@str,charIndex(' ',@str,1))
begin
set @value=left(@str,charindex(' ',@str,1))
IF @value LIKE '%,%' OR @value LIKE '%.%'
begin
SET @VALUE=left(@value,len(@value)-1)
end
end
if exists (select * from @temptable where word=@value)
update @temptable set word_count=word_count+1 where word=@value
else
insert into @temptable select @value,1,LEN(@value)
set @str= RIGHT(@str, Len(@str)-CharIndex(' ',@str,1))
end
select * from @temptable
Output
If you
have any issue regarding SQL & BI where you are stuck write to me in the
comment box I will try to help you out.
