Posted by news.demon.co.uk on 04/16/07 09:15

Hi All,

let me try to explain what I'm trying to accomplish - I really hope someone
can help.

I have a table (tblArticles) which has the following:

vcrKeywords varchar(2000)
txtBody text(8000)
vcrType varchar(128)

and this is a sample of the data

vcrKeyWords || txtBody || vcrType
key1,key2,key4,key7,key9 || <snipped body> || Site5 News
key1,key3,key6,key8,key9 || <snipped body> || Site5 News
key1,key3,key4,key5,key9 || <snipped body> || Site5 News
key1,key2,key5,key7,key8 || <snipped body> || Site5 News

What I'm trying to accomplish is to return a keyword count based on the
content of vcrKeywords (i.e. each comma seperated entry as a count.

My SQL statement originally was :

SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticles
where vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESC

However, this simply matches the entire vcrKeyword Column and not each comma
seperated value.

Is there a way in SQL that I can achieve this or do I need to use some kind
of scripting language to accomplish it...?

Regards,

Carl.

[Back to original message]