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.
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: