SQL SERVER - count words in string

,
1

Hi,

I have a table with the columns: word, category, description, countWord.
I need to check for each and every word in column word, how many times it’s exists in the description column for every category and set the countWord column:
example:
image

Thanks,
S

2

The following should work for you...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	WORD VARCHAR(50) NOT NULL,
	Description VARCHAR(1000) NOT NULL,
	countWord INT DEFAULT (0)
	);
INSERT #TestData (WORD, Description)
VALUES ('Acrylics', 'This is something about Acrylics and Acrylics accessories.');

UPDATE td SET 
	td.countWord = (x.DescLen - x.RepLen) / x.WordLen
FROM 
	#TestData td
	CROSS APPLY ( VALUES (
						LEN(td.WORD), 
						LEN(td.Description),
						LEN(REPLACE(td.Description, td.WORD, ''))
						) ) x (WordLen, DescLen, RepLen);

SELECT 
	*
FROM 
	#TestData td;
3

Thanks a lot Jason for the quick and efficiency code.
i ran it on ~950,000 rows and it's finished after 2.5 minutes :smile:

Thanks a lot.
S

4

Glad to help. :slight_smile: