asked on

T-SQL qts : Count the number of times a word appears in a column

The end result should be the number of times the word appeared in the column. Please note that this is not same as the rowcount in which the word appears.

So lets say there is a column called 'Sentance' and it contains many rows... couple of which are :

ROW 1> Howdy how are you? Nice to meet you.
ROW 2> Hey there howz it going? Good seeing you. Met your friend earlier.

If the query searched just these two rows for word 'you' the result should be 3. (Note that it shouldn't be 4 even though 'your' contains the substring 'you').

PFrog

The problem you will find is differentiating between "you" and "your". The only way I can think of this is to look for " you " or " you." or " you?" or " you!" etc., but this will be difficult to make complete and reliable.

Try this though...

IF OBJECT_ID('dbo.CountInString') IS NOT NULL
      DROP FUNCTION CountInString
GO

CREATE FUNCTION dbo.CountInString (@SearchStr varchar(max), @SearchIn varchar(max))
      RETURNS INT
AS
BEGIN
      DECLARE @newSearchIn varchar(max)
      SET @newSearchIn = ' ' + @SearchIn + ' '
      SET @newSearchIn = REPLACE(@newSearchIn,' ' + @SearchStr + ' ', ' ')
      SET @newSearchIn = REPLACE(@newSearchIn,' ' + @SearchStr + '.', ' .')
      SET @newSearchIn = REPLACE(@newSearchIn,' ' + @SearchStr + '!', ' !')
      SET @newSearchIn = REPLACE(@newSearchIn,' ' + @SearchStr + '?', ' ?')

      RETURN (len(@SearchIn) - (len(@NewSearchIn)-2))/len(@SearchStr)
END
GO

SELECT dbo.CountInString('you','Howdy how are you? Nice to meet you.')
SELECT dbo.CountInString('you','Hey there howz it going? Good seeing you. Met your friend earlier.')

shahprabal

ASKER

PFrog thats an ingenious approach, however the function should be able to search the whole cloumn. Is there a way to make it do that other than running a select and then passing the rows to the function?

ASKER CERTIFIED SOLUTION

PFrog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shahprabal

ASKER

:) impressive indeed