[Sqlserver] query the number of occurrences of a string in a text field

-- [Sqlserver] declare @ tablenvarchar (40); -- Query table name declare @ textfieldnvarchar (40); -- Query column name (text type) declare @ findstrnvarchar (400); -- query string (not empty) declare @ stepint

-- [Sqlserver] calculate the number of times a string appears in a text field. declare @ table nvarchar (40); -- query the table name declare @ textfield nvarchar (40 ); -- query the column name (text type) declare @ findstr nvarchar (400); -- query the string (not empty) declare @ step int

-- [Sqlserver] calculate the number of times a string appears in a text field

Declare @ table nvarchar (40); -- query the table name
Declare @ textfield nvarchar (40); -- query the column name (text type)
Declare @ findstr nvarchar (400); -- query string (not empty)
Declare @ step int; -- the length of each string intercepted from the text field
Declare @ findcount int; -- number of occurrences
Declare @ SQL nvarchar (400 );
Declare @ str nvarchar (4000 );
Declare @ findpos int;
Declare @ pos int;
Declare @ innerpos int;

Set @ table = 'content ';
Set @ textfield = 'aboutus ';
Set @ findstr ='';
Set @ step = 1000;
Set @ pos = 1;
Set @ findpos = 0;
Set @ findcount = 0;

While (@ pos = 1 or len (@ str) = @ step)
Begin
Set @ SQL = n' select @ str = substring (cast ('+ @ textfield +' as ntext), '+ cast (@ pos as nvarchar) + ', '+ cast (@ step as nvarchar) +') from '+ @ table;
-- Select @ SQL;
Exec sp_executesql @ SQL, n' @ str nvarchar (4000) output', @ str output;
-- Select @ str;
Set @ innerpos = 1;
While (@ innerpos = 1 or @ findpos> 0)
Begin
Select @ findpos = charindex (@ findstr, @ str, @ innerpos );
If @ findpos> 0
Set @ findcount = @ findcount + 1;
Set @ innerpos = @ findpos + len (@ findstr );
End
Set @ pos = @ pos + len (@ str)-(len (@ findstr)-1 );
End

Select @ findcount;