Recently I have been in a scenario where I need to count repetition of different given word in row of column and determine final outcome based on the count of them.
Here is what I came up with.
CREATE FUNCTION [dbo].[fn_wordCount] (@string varchar(8000),@word varchar(50))
RETURNS INT
/*
Purpose: returns the number of given word in @string
Author: Abhishek Joshi
Date: April 13th 2012
Example:
SELECT dbo.fn_wordCount('PAID_CB_PAID_CB_PAID_CB_CB_PAID_PAID','CB')
would return: 4
*/
AS
BEGIN
--SET @string = LTRIM(RTRIM(ISNULL(@string,'')));
IF LEN(@string) = 0 RETURN 0;
-- return the difference in length after stripping spaces, this is the word count
RETURN (LEN(@string) - LEN(REPLACE(@string,@word,'')))/LEN(@word);
END
Thanks to http://www.websolete.com/posts/t-sql-function-to-get-word-count
for the idea.
