Hi,

I am trying to make a term from a string into the subject and then be
able to count the occurences of this.

using:

Where (Fail_Desc like '%C[1-999]%' or Fail_Desc like '%U[1-99]%')

Which would output:

Fail_Desc
Replace Capacitor C123
Replace Capacitor C145
Replace Capacitor C123
Replace Device U12

And I would like it to look like:

Failing Component       Qty
C123                            2
C145                            1
U12                              1


Any ideas would be much appreciated, thanks.

Phil

--Try this:
select substring(Fail_desc, patindex(fail_desc,'%C[1-999]%'),5)
'Failing Component, count(*)
from myTable
group by fail_desc

--if that group by doesn't take... use this
group by substring(Fail_desc, patindex(fail_desc,'%C[1-999]%'),5)

On 21 Jan 2006 07:52:25 -0800, philipbennett25 wrote:

Show quoteHide quoteHi Phil,

Try if this works:

SELECT   Component, COUNT(*) AS Qty
FROM    (SELECT SUBSTRING(Fail_Desc,
                          PATINDEX('%C[1-9][0-9][0-9]%', Fail_Desc),
                          4)
         FROM   test
         WHERE  Fail_Desc LIKE '%C[1-9][0-9][0-9]%'
         UNION ALL
         SELECT SUBSTRING(Fail_Desc,
                          PATINDEX('%U[1-9][0-9]%', Fail_Desc),
                          3)
         FROM   test
         WHERE  Fail_Desc LIKE '%U[1-9][0-9]%') AS x(Component)
GROUP BY Component

If this is not what you want, then please check www.aspfaq.com/5006 for
a description of the best way to post better specifications.

Hey,

Thanks for that, it was a huge help. The funny thing about this is that
when I run the query it returns a bunch other stuff as well as the 'C%'
or the 'U%' is this normal?

Thanks

Phil

On 27 Jan 2006 05:13:32 -0800, philipbennett25 wrote:

Hi Phil,

No, that should not happen. Can you post a repro script (i.e. a short
script that I can run in my test DB to reproduce the behaviour - it
should include CREATE TABLE and INSERT statements, plus the offending
SELECT of course).

Order By Primary Key
Select where result are by the best match
where current of cursorName
Really really slow cursor
Problem copying data from column in same table