I'm trying to create a T-SQL query that counts the number of times a word displays in a specific record. For example, I have the query
SELECT long_desc FROM table
Can I determine how many times the word stock appears across all instances of the column long-desc?
T-SQL doesn't provide particularly rich support for string operations such as this one. You could get better performance and richer functionality for solving the problem in the question by using full-text indexing in SQL Server 2000 and 7.0. However, I can describe a T-SQL solution and a set-based solution that work.
SQL is a set-based language, and set-based languages don't provide easy ways to work with subsets of data in a column. To build the query you want, you must first determine how many times the target word, stock, exists in each row.
T-SQL doesn't provide a built-in function to count the number of times a particular string appears within another string, so to find out how many times a word appears in a row, you have to build your own count function. SQL Server 2000 lets you create this kind of user-defined function (UDF). Listing 3 shows a UDF called WordRepeatedNumTimes() that accepts two parameters: @SourceString and @TargetWord. The function returns an integer value that represents the number of times that @TargetWord is repeated in @SourceString.
I've created a sample test to show how you can use this function to determine how many times a word appears across all instances of a column. Create the function in a database called TEST, and create the two tables titles and titles_big by running the script that Listing 4 shows. Titles is a copy of the titles table from the Pubs database. Titles_big has the same structure as titles, but I've removed the primary key and created a table that has 18,000 rows.
The following query counts the number of times that the word the appears in the titles column of the titles table:
SELECT SUM(dbo.WordRepeatedNumTimes
(title, 'the'))
FROM titles
Prev. page
[1]
2
next page 
