Word count and group 1

[damipera]

Hi Guys,

I am struggling with this problem can you give me some suggestions please.

Thanks.


this is the table

Date         dataColumn
1/23/2012  - blah blah Word1 blah Word2 blah blah Word4 bla Word4 blah Word3 blah Word5


2/20/2012  - blah blah Word1 blah blah Word4 blah blah


3/14/2012 -  blah blah Word3 blah blah Word1 blah blah Word2

where:
Word1 and Word2 - counted together and classed as Good
Word3, Word4, Word5 - counted together and classed as Bad

and I want to look the recordset like this:

recordset
Date         GoodColumn   BadColumn
1/23/2012 -       2           4
2/20/2012 -       1           1
3/14/2012 -       2           1

 

[r937]

SELECT Date
     , ( LEN(dataColumn) -
         LEN(REPLACE(dataColumn,'Word1','')) ) /
         LEN('Word1')
     + ( LEN(dataColumn) -
         LEN(REPLACE(dataColumn,'Word2','')) ) /
         LEN('Word2')
       AS GoodColumn
     , ( LEN(dataColumn) -
         LEN(REPLACE(dataColumn,'Word3','')) ) /
         LEN('Word3')
     + ( LEN(dataColumn) -
         LEN(REPLACE(dataColumn,'Word4','')) ) /
         LEN('Word4')
     + ( LEN(dataColumn) -
         LEN(REPLACE(dataColumn,'Word5','')) ) /
         LEN('Word5')
       AS BadColumn
  FROM ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[damipera]

SIMPLY AWESOME!

Thanks very much r937! You made my day.