homepage Welcome to WebmasterWorld Guest from 54.167.185.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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




msg:1578566
 5:48 pm on Oct 18, 2005 (gmt 0)

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




msg:1578567
 8:32 pm on Oct 18, 2005 (gmt 0)

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




msg:1578568
 9:55 pm on Oct 18, 2005 (gmt 0)

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




msg:1578569
 12:15 am on Oct 19, 2005 (gmt 0)

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))

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved