Making a term found in a string the subject

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:

>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.
Hi 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.
Hugo Kornelis, SQL Server MVP|||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:

>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
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).
Hugo Kornelis, SQL Server MVP