none
Count a word in one column

    Question

  • how can i count a particular word in one column.

    Example ::

    Desc(coulmn)
    {this website is designed with Asp. Asp is very easy.Using Asp we can design very fast.
    }


    From the above cloumn my requirement is, to get the count of Asp word in Desc cloumn Asp is repeated for 3 time so, the count should be 3
    Can any one help me the exact query for this one


    Thursday, September 17, 2009 12:31 PM

Answers

  • DECLARE @x VARCHAR(10), @y VARCHAR(64)

    SELECT @x = 'asp', @y = 'this website is designed with Asp. Asp is very easy.Using Asp we can design very fast.'
     
    SELECT 
        (DATALENGTH(@y) - DATALENGTH
        (
            REPLACE(LOWER(@y), LOWER(@x), '')
        ))
        / DATALENGTH(@x) AS Count

     

     

     

    Thursday, September 17, 2009 1:35 PM

All replies

  • DECLARE @x VARCHAR(10), @y VARCHAR(64)

    SELECT @x = 'asp', @y = 'this website is designed with Asp. Asp is very easy.Using Asp we can design very fast.'
     
    SELECT 
        (DATALENGTH(@y) - DATALENGTH
        (
            REPLACE(LOWER(@y), LOWER(@x), '')
        ))
        / DATALENGTH(@x) AS Count

     

     

     

    Thursday, September 17, 2009 1:35 PM
  • Thanq !

    It's Working

    But sometimes it is showing -1 of entire output.


    { If your make  computer make or network is protected by a firewall or proxy, make vijay sure}

    for this data the a word make count only 2 times.

    Is there any reason for that
    Thursday, September 17, 2009 3:33 PM