asked on

counting strings in ntext field

Attempting to count a string in an ntext field

for example
the ntext field test has "ted, bob, test, sue, ted"
I want to count the occurences of "ted" in test

I've looked through the various answers provided here re: counting substrings and haven't been successful in modifying them to work for this purpose.

paelo

The major problem as I see it is working with the text field. I think there is some way of chunking it up and parsing the entire thing but anything I can conceive would be terribly inefficient.

If its possible to store the text in a varchar(8000) then you can do something like this:

--declarations
DECLARE @X varchar(30),
@N varchar(8000)

--set find value, convert text field
SELECT @X='Ted', @N=CONVERT(Varchar(8000),textfld)

--print number of occurences of that string (including instances within a word, ie. this will count the TED in faTED)
PRINT (LEN(@N)-LEN(REPLACE(@N,@X,'')))/LEN(@X)

-Paul.

jdlambert1

Only ways I know of to do this are to use a cursor in a Stored Procedure, or create the logic in an Extended Stored Procedure.

In an SP, create two counter variables, one to track the starting position in the string and the other to count the hits. Plug the first one into SubString's starting position and loop (with a cursor) until you get to the end of the string, then store the total and go to the next string.

ASKER CERTIFIED SOLUTION

Ken Selvia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slinman2

ASKER

Thanks for all the responses. I took the approach of kselvia and it worked out great. thanks.