Number of times a string repeats

[samcam]

Hi Folks,

I need to find out how many times a string repeats within another string. For Instance, I have this string
"How much wood could a woodchuck chuck if woodchuck could chuck wood"

and I need to find out how many times the word wood appears within this string...

Please help.

Thanks

 

[tlbroadbent]

Try this.

Declare @str varchar(4000), @substr varchar(30)
Set @str = 'How much wood could a woodchuck chuck if woodchuck could chuck wood'
Set @substr = 'wood'
Select OccursNo=(Len(@str)-len(replace(@str,@substr,'')))/len(@substr)

I found this technique at

http://www.umachandar.com/technical/SQL70Scripts/Main42.htm

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[samcam]

Actually my string is a varchar field in a table and I need to find out how many times a word repeats itself in this string field.

 

[tlbroadbent]

Modify the code to select the column fronm the table instead of using a variable. It still works. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[samcam]

Declare @str varchar(8000), @substr varchar(10)
Set @str = (select case_history from table_case where id_number = '10075')
Set @substr = 'Phone'
Select OccursNo=(Len(@str)-len(replace(@str,@substr,'')))/len(@substr)

This is what I tried and I am getting this error

"The text, ntext, and image data types are invalid in this subquery or aggregate expression."

Where am I messing it up....

Thanks a lot for taking your time to help me out

 

[tlbroadbent]

IS the column varchar or text? The message indicates it is text.

You can simplify in this manner unless the column is a text or ntext data type.

Set @substr = 'Phone'
Select OccursNo=
(Len(case_history) - len(replace(case_history,@substr, ''))) / len(@substr)
from table_case where id_number = '10075'

Try this if the column is text type. This will allow you to search the first 800 characters of the text column.

Select OccursNo=
(Len(convert(varchar(8000), case_history)) - len(replace(convert(varchar(8000), case_history),@substr, ''))) / len(@substr)
from table_case where id_number = '10075'

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[samcam]

Thank you very much. You are a genius..

I used this and it works like a charm..

Select OccursNo=
(Len(convert(varchar(8000), case_history)) - len(replace(convert(varchar(8000), case_history),'Phone Log', ''))) / len('Phone Log')
from table_case where id_number = '10075'