I bet this is simple, -But see if you can help. Hard to come up with Title -Have a lo

Hi,
What I would like to be able to do in simple SQL is to group on a term
that has come from a longer string. As below.
I begin with data in this format.
PARTNO DESC
12345-01 Capacitor C1 requires resoldering
12345-01 Capcitor broken, replace C1
12345-01 Solder Quality IC U1
12345-01 U1 Misaligned
25698-05 R115 Cracked
25698-05 Dry solder joing R115
And would like it to appear like this.
PARTNO DESC Count
12345-01 C1 2
12345-01 U1 2
25698-05 R115 2
If you could help that would be awesome. I also have to find the faults
in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
Many Thanksphilipbennett25 wrote:
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
It would help if yuo could create a table of the descriptsion first:
CREATE TABLE part_descriptions (descr VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO part_descriptions (descr)
SELECT 'C1' UNION ALL
SELECT 'U1' UNION ALL
SELECT 'R115' ;
Then try this:
SELECT T.partno, P.descr, COUNT(*) AS cnt
FROM tbl AS T
JOIN part_descriptions AS P
ON T.descr LIKE '%'+P.descr+'%'
WHERE P.descr LIKE '%'
GROUP BY T.partno, P.descr ;
You could also make use of PATINDEX:
DECLARE @.p VARCHAR(20)
SET @.p = 'C1'
SELECT partno, descr, COUNT(*) AS cnt
FROM
(SELECT partno, SUBSTRING(descr,PATINDEX('%'+@.p+'%',desc
r),LEN(@.p))
FROM tbl
WHERE descr LIKE '%'+@.p+'%') AS T(partno,descr)
GROUP BY partno, descr ;
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL,
col VARCHAR(50) NOT NULL
)
INSERT INTO #Test VALUES ('20050101','Hello world C1')
INSERT INTO #Test VALUES ('20050101','Hello C1 world ')
INSERT INTO #Test VALUES ('20050201','M favorite is D1')
INSERT INTO #Test VALUES ('20050201','D1 Is my Favorite ')
SELECT dt,descr,COUNT(*)
FROM
(
SELECT dt,CASE WHEN col LIKE '%c1%' THEN 'c1'
ELSE CASE WHEN col LIKE '%d1%'THEN 'd1' END END descr
FROM #Test
) AS D GROUP BY dt,descr
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1133779185.731612.3100@.g49g2000cwa.googlegroups.com...
> Hi,
> What I would like to be able to do in simple SQL is to group on a term
> that has come from a longer string. As below.
> I begin with data in this format.
> PARTNO DESC
> 12345-01 Capacitor C1 requires resoldering
> 12345-01 Capcitor broken, replace C1
> 12345-01 Solder Quality IC U1
> 12345-01 U1 Misaligned
> 25698-05 R115 Cracked
> 25698-05 Dry solder joing R115
> And would like it to appear like this.
> PARTNO DESC Count
> 12345-01 C1 2
> 12345-01 U1 2
> 25698-05 R115 2
>
> If you could help that would be awesome. I also have to find the faults
> in the description using multiple wildcards. eg -WHERE DESC Like 'C__'
> Many Thanks
>|||Hey guys, thanks for your help on this. The Database I am querying is
not a SQL Server therefore it has to be done using basic SQL. It also
needs to be able to grow and shrink depending on what it Finds in the
DESC Field. I will be searching for the keywords in the DESC column by
looking for patterns, not having specific keywords already defined. IE
I would just have "Like 'C_' ".
Many Thanks