I need to know if a varchar field has a certain number of words in it

Is there way to do this as part of the WHERE clause in a SELECT statement?

[GaryK]

Here's the basics.

I run a website and use a weighting system to determine how many points a member is awarded. The points are used as a guide to how knowledgeable the member is about the website's topic.

So far it's working well.

I need to tweak one portion of the stored procedure that calculates the points in order to reduce the impact of forum replies that contain less than ten words.

SET @Points_RepliesPosted = (
SELECT
COUNT(UniqueID)
FROM
tblMB
WHERE
(ForumID NOT IN (8,10,14,15)) AND
(MsgType = 1) AND
(MemberID = @MemberID) AND
(DATEDIFF(day, MsgDateTime, @CONST_DateNow) < @CONST_RecentDays) AND
(there are at least ten words in MsgBody))

Is there a way to calculate the number of words (or spaces) in MsgBody in the above SELECT statement and within the WHERE section?

Thanks.

[aspdaddy]

I dont know if this works in SQL Server as I'm not at a PC with itinstalled but one way of approaching this is to calculate the difference in the length of the string to the length of the string with all the spaces removed:

Declare @intWords AS tinyint
SET @intWords = (SELECT Len(strText)-Len(Replace(strText,' ',''))

Then use @intWords >10 in the WHERE clause

[GaryK]

That seems like it should work. I'll test it after supper and report back on what happened. Either way, many thanks for replying.

[GaryK]

I wanted to keep it all in one query so here's what I did. It works great. It's not terribly efficient, but it's only running four times a day on a few thousand rows. The row count never goes above 3,000 so I'm sure it will be alright.

Thanks again. You've helped me solve a huge problem. I am in your debt.

SET @Points_RepliesPosted = (
SELECT
COUNT(UniqueID)
FROM
tblMB
WHERE
(ForumID NOT IN (8,10,14,15)) AND
(MsgType = 1) AND
(MemberID = @MemberID) AND
(DATEDIFF(day, MsgDateTime, @CONST_DateNow) < @CONST_RecentDays) AND
(LEN(MsgBody) - LEN(REPLACE(MsgBody,' ','')) > 10))