Count number of each word in a string in SQL

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.