Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Counting No Of Words In a Column

    Hi,


    for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.

    for example, if the column data is,'This Is A Test', the function, will return 4.
    pl suggest any other efficient strategies to accomplish this


    thanks
    Attached Files Attached Files
    Cheers....

    baburajv

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this too simple?

    Code:
    DECLARE @Text AS VarChar(100)
    SET @Text = 'This is a sentence'
    PRINT  'Number of words: ' + CAST(LEN(@Text) - LEN(REPLACE(@Text, ' ', '')) +1 AS VarChar(3))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - if it isn't then it is vastly more efficient.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by pootle flump
    Is this too simple?

    Code:
    DECLARE @Text AS VarChar(100)
    SET @Text = 'This is a sentence'
    PRINT  'Number of words: ' + CAST(LEN(@Text) - LEN(REPLACE(@Text, ' ', '')) +1 AS VarChar(3))

    one more qn..

    wat if the data contains blank space

    e.g. : 'This Is A Sentence '
    Cheers....

    baburajv

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Bol

    Rtrim(ltrim())
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by Brett Kaiser
    Bol

    Rtrim(ltrim())

    Thank U all for the comments...

    Cheers....

    baburajv

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A bigger issue would be if the text contains double spaces:
    Code:
    "This is a single line of text.  This text contains two sentences separated by two space characters."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    A bigger issue would be if the text contains double spaces:
    Code:
    "This is a single line of text.  This text contains two sentences separated by two space characters."
    Code:
    LEN(REPLACE(@Text, '  ', ' ')) - LEN(REPLACE(REPLACE(@Text, '  ', ' '), ' ', '')) +1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You're gonna ask about triple spaces now ain't cha?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yup. I am.
    To make this robust, you need a loop to eliminate double spaces until none remain. And that means this should be a multi-step function rather than a simple formula.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    ... you need a loop ...
    muthaf*$#%$n loops in a muthaf*$#%$n function?

    next you're going to suggest using a cursor, aren't you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •