SQL Server Performance

Word count

Discussion in 'SQL Server 2005 General Developer Questions' started by jeyakumars, Dec 1, 2006.

  1. jeyakumars New Member

    Dear All,
    I need to find out the words count in a single column.
    Ex :

    col1
    'Thank You Very Much'

    I need to display the count of the words in that column.

    can u pls help me out?

    Thanks in Advance

    Jai

  2. Adriaan New Member

    A = the length of the string using LEN().
    B = the length of the string after using REPLACE() to remove any spaces.
    Then A - B is the number of spaces in the string.
    And finally (A - B) + 1 is the number of words.

    SELECT
    (LEN(column) - LEN(REPLACE(column, ' ', ''))) + 1
    FROM table
  3. jeyakumars New Member

    Thank you Adriaan
  4. madhuottapalam New Member

  5. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by madhuottapalam</i><br /><br />Hi,<br /><br />This artile will give u a better idea<br /><br /<a target="_blank" href=http://www.sql-server-helper.com/functions/count-words.aspx>http://www.sql-server-helper.com/functions/count-words.aspx</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />The UDF from your link isn't terribly thrilling, if you ask me. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />I don't see a reason for a UDF here anyway and especially not for a UDF that walks through the input string character by character. Adriaan's solution is pretty good. Probably even better would be to do this at the front end. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  6. xiebo2010cx Member

    Subscribing here

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  7. xiebo2010cx Member

    Subscribing here

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  8. MohammedU New Member

    Doing at the front end is better or you should CLR functions for this type of fuctionality.


    Mohammed U.
  9. DilliGrg Member

    Speaking of the function, there is an interesting point mentioned which relates to Adriaan's solution. Look at this example with space: It should return 4 words instead of returning 11, right?



    CREATE TABLE #test
    (SomeValue varchar(50)
    )
    INSERT INTO #test(SomeValue )
    VALUES ('Thank You Very Much')

    SELECT
    (LEN(SomeValue ) - LEN(REPLACE(SomeValue , ' ', ''))) + 1 AS [Number of Words]
    FROM #test



    drop table #test

    Number of Words
    ---------------
    11

    (1 row(s) affected)


    Thanks,
    DilliGrg
  10. FrankKalis Moderator

    Interesting. A good reason to do this at the client. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  11. Adriaan New Member

    DECLARE @string
    SET @string = 'xxx x xxxxxxxxxxx'

    WHILE @string LIKE '% %'
    BEGIN
    SET @string = REPLACE(@string, ' ', ' ')
    END

    ... and finish like before. It never stops - you can always think of exceptions.
  12. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Interesting. A good reason to do this at the client. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">This is what I prefer to say [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  13. FrankKalis Moderator

    I know, but you were very late on this one, so I jumped in. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  14. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />I know, but you were very late on this one, so I jumped in. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Thanks,<br />DilliGrg
  15. FrankKalis Moderator

    Hey, isn't it a bit late (or early, for that matter) in the place where you live? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. DilliGrg Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hey, isn't it a bit late (or early, for that matter) in the place where you live? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Frank,<br />It's almost 1am. Going late in the morning for work. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Thanks,<br />DilliGrg
  17. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Hey, isn't it a bit late (or early, for that matter) in the place where you live? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Did you ask this question to me?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  18. FrankKalis Moderator

    No. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page