We help IT Professionals succeed at work.

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

shahprabal
shahprabal asked
on
1,372 Views
Last Modified: 2008-03-21
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').




Comment
Watch Question

Commented:
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.')

Author

Commented:
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?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
:) impressive indeed
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.