SQL Select GROUP BY part of field if has a special char

Question

Is there a way to GROUP BY a part of a string....

I wanted to create a SQLFIDDLE but they seem to have to serverproblems, so I have to make it visible here....

This would be the data...

CREATE TABLE tblArticle
    (
     id int auto_increment primary key, 
     art_id varchar(20), 
     art_name varchar(30),
     art_color varchar(30),
     art_type varchar(5)
    );

INSERT INTO tblArticle
(art_id, art_name, art_color, art_type)
VALUES
('12345-1','Textile', 'Black','MAT'),
('12345-2','Textile', 'Red','MAT'),
('12345-3','Textile', 'Green','MAT'),
('12345-4','Textile', 'Blue','MAT'),
('54321-1','Textile', 'Black','MAT'),
('54321-2','Textile', 'Red','MAT'),
('54321-3','Textile', 'Green','MAT'),
('54321-4','Textile', 'Blue','MAT');

So I get some like:

| id        | art_id   | art_name | art_color | art_type |
----------------------------------------------------------
| 1         | 12345-1  | Textile  | Black     | MAT      |
| 2         | 12345-2  | Textile  | Red       | MAT      |
| 3         | 12345-3  | Textile  | Green     | MAT      |
| 4         | 12345-4  | Textile  | Blue      | MAT      |
| 5         | 54321-1  | Textile  | Black     | MAT      |
| 6         | 54321-2  | Textile  | Red       | MAT      |
| 7         | 54321-3  | Textile  | Green     | MAT      |
| 8         | 54321-4  | Textile  | Blue      | MAT      |
| 9         | 9876543  | Textile  | White     | MAT      |
----------------------------------------------------------

My select looks like

Select art_id, art_name FROM tblArticle WHERE art_type = 'MAT' GROUP BY art_name

What I need is the art_id (doesn't matters if its with -1 or -2 and so on) and the art_name to do further querys.

As you can see I have 2 different art_id groups... and I want to group by them.

So I get two groups... 12345 and 54321. But I don't even know how to start ^^

Expacted result:

12345,   Textile
54321,   Textile
9876543, Textile

I tried to add art_id to my group by but the has the same effect like don't use group by ^^

What could I do to achieve this ?

SOLVED like:

SELECT DISTINCT @IF( @SCAN( art_id, '-' ) +1, 
                     @LEFT( art_id, @SCAN( art_id, '-')),
                     art_id) AS art_id, art_name
FROM                 tblArticle
WHERE                art_type LIKE '%MAT%';

In this case the DISTINCT has the same effect like a GROUP BY.

+1 is used to get 0 if scan could not find anything. Actaully it returns -1 if there was no found. But IF needs 0 (false) or 1+ (true). And there will never be a - in fist place in my case.

Could not use GROUP BY because it only accepts integer or column.

Answer 1

You could try this, It will work out where the dash is and group by properly

Select case when Instr(art_id, '-') = 0 
              then art_id 
              else Left(art_id, Instr(art_id, '-') - 1) end, art_name 
FROM tblArticle 
WHERE art_type = 'MAT' 
GROUP BY case when Instr(art_id, '-') = 0 
              then art_id 
              else Left(art_id, Instr(art_id, '-') - 1) end
,art_name

Answer 2

In order to group, you must specify which bit of the string to group by. In this case it's the first 5 characters so you would use LEFT(art_id,5). This must be matched in the SELECT statement so you will need to modify it to read the same. I have aliased the column to be art_id otherwise it will be unknown:

SELECT       LEFT(art_id,5) AS art_id, art_name 
FROM         tblArticle 
WHERE        art_type = 'MAT' 
GROUP BY     LEFT(art_id,5), art_name

The only thing that could be a problem is if you begin to have IDs bigger than 5 characters. In this case you will need to use @FIND in order to look for the dash and take the left of that. This will crash if there's no dash, as the @FIND function returns -1 if no match is found, so we have to use an @IF statement to compensate for this.

In which case I would write:

SELECT       @IF(art_id LIKE '%-%'
                 ,@LEFT(art_id, @FIND('-', art_id, 0) - 1)
                 ,art_id
                 ) AS art_id, art_name 
FROM         tblArticle 
WHERE        art_type = 'MAT' 
GROUP BY     @IF(art_id LIKE '%-%'
                 ,@LEFT(art_id, @FIND('-', art_id, 0) - 1)
                 ,art_id
                 ), art_name

The @ symbols are necessary (at least I think they are, try without if it doesn't work), I've not used SQLBase before so I'm using the following official documentation as a guide:

GUPTA SQLBase - SQL Language Reference

Answer 3

If you do not care about the other column meaning doing group by will select random values from other column in this case the art_name and it could be a random one from one of art_id you can use as

select 
substring_index(art_id,'-',1) as atr_id_val , 
art_name 
from tblArticle 
where art_type = 'MAT'
group by atr_id_val ;

If you need to select all the columns and need to make sure all the columns are from last entry of number-{1 or 2} you can use as

select 
t1.id,
substring_index(t1.art_id,'-',1) as art_id, 
t1.art_name , 
t1.art_color , 
t1.art_type from tblArticle t1 
left join tblArticle t2 on substring_index(t2.art_id,'-',1) = substring_index(t1.art_id,'-',1) and t1.id < t2.id and t1.art_type = 'MAT'  where t2.id is null ;

Answer 4

The art_id trimming needs to be included in select also I believe.

Select left( art_id, iif(CHARINDEX('-',art_id) > 0,CHARINDEX('-',art_id)-1, len(art_id) )) art_id, art_name 
    FROM tblArticle 
    WHERE art_type = 'MAT' 
    GROUP BY left ( art_id, iif(CHARINDEX('-',art_id) > 0,CHARINDEX('-',art_id)-1, len(art_id) ))
    ,art_name