Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 25th, 2003, 08:01 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Word Count

Quick question:

If I have a table with one of the columns being the text of a message, what is the easiest way to do a query that returns a couple fields out of the table and the number of words in the message? Is this best done client or server side?
Reply With Quote
  #2 (permalink)  
Old July 25th, 2003, 08:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would have said client-side. You are not querying a database, you are programmatically parsing a particular field.

That field may have been returned using a server-side query, but you are processing the field on the client.

If you need to do queries like the one you suggested on the server, then maybe you need to consider how you have normalised your data.

It does look like you just need to do it as a one off though, so your data may be perfectly normalised - I leave that distinction to you :)

Regards
Owain Williams
Reply With Quote
  #3 (permalink)  
Old July 25th, 2003, 08:26 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, thanks for the input. I would prefer to do it in SQL, but I think you are right. I'll do it on the client.

Thanks again for the advice.
Reply With Quote
  #4 (permalink)  
Old July 25th, 2003, 08:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

There's a quick kludge to do stuff like this, depends how bothered you are about the intricacies of what makes up a word. If we assume that each word is separated by a space, but there won't be a space at the end of the final word, then all you need to do is count the number of spaces in the string and add 1.
Code:
SELECT (1 + LEN(Field) - LEN(REPLACE(Field, ' ', ''))) AS WordCount
Reply With Quote
  #5 (permalink)  
Old July 25th, 2003, 08:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Will then LEN and REPLACE functions work in SQL Server (or other RDBMS's for that matter) or are they Access only functions?

Regards
Owain Williams
Reply With Quote
  #6 (permalink)  
Old July 25th, 2003, 08:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

I never even thought about Access! LEN and REPLACE are available in SQL Server.
Reply With Quote
  #7 (permalink)  
Old July 25th, 2003, 09:03 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you pgtips. I was trying to think of a way to replace all non space characters and then get the len of the remaining spaces, but yours is much easier.

The only problem I can see is that when people put 2 spaces at the start of a sentence, but it doesn't have to be totally exact.
Reply With Quote
  #8 (permalink)  
Old July 25th, 2003, 09:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hey, like I said its a kludge :).

There is another problem - empty strings have a word count of 1! You can fiddle around with this stuff to your heart's content, e.g. replace double spaces with one space before doing the count, and do a separate test for empty strings. The point is its an easy way to get an approx word count...
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word search - Count Hits bonekrusher XSLT 5 January 2nd, 2008 10:26 AM
how to count lines in word file arshad mahmood VB How-To 2 June 7th, 2006 09:40 PM
character count of ms-word in asp.net sh.rajkumar ASP.NET 1.0 and 1.1 Professional 0 May 19th, 2006 07:24 AM
Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM
Coding for Word Count zadiac8318 JSP Basics 1 February 26th, 2005 07:34 PM



All times are GMT -4. The time now is 10:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.