Posted by Plamen Ratchev on 04/16/07 13:29

I agree with Jason that normalizing the design of the table for keywords
will be most beneficial. It adds so much power to what you can do with those
keywords.

A couple notes that may help too, especially if you are not in a position to
change table design:

- Scripting and client side languages have a very good support to deal with
lists and arrays. Most of them implement some sort of "split" function where
passing a list and delimiter as parameters will give you an array that will
be much easier to deal with. Plus they do it fast.

- Erland Sommarskog has excellent articles on Arrays and Lists in SQL Server
that can help you to normalize the existing data and use it more
efficiently:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html

- If you just need the count of keywords and your keyword delimiter is
always a comma, then you can write something like this to get the count:

SELECT vcrKeywords,
CASE
WHEN LEN(vcrKeyWords) = 0 THEN 0
ELSE
LEN(vcrKeyWords) - LEN(REPLACE(vcrKeyWords, ',', '')) + 1
END AS keycount
FROM tblArticles
WHERE vcrType LIKE 'site 5%'
GROUP BY vcrKeyWords
ORDER BY keycount DESC

Not sure if your keywords column is a blank string or NULL when there are no
keywords associated, so you can tune up the first CASE condition accordingly
to handle the case when there are 0 keywords.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]