homepage Welcome to WebmasterWorld Guest from 54.237.213.31
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 102 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 102 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 102 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 102 posted 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