By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable1 (name CHAR(5), sys VARCHAR(16), id VARCHAR(16));
INSERT INTO mytable1 VALUES
('name1', 'aaa,bbb,ccc', '8,3,5'),
('name2', 'ccc,bbb', '22,33'),
('name3', 'ccc,ccc', '11,56'),
('name4', 'aaa,ccc,ccc,ccc', '87,88,40,99'),
('name5', 'aaa,bbb', '1,2');
SELECT * FROM mytable1;
| name | sys | id |
|---|---|---|
| name1 | aaa,bbb,ccc | 8,3,5 |
| name2 | ccc,bbb | 22,33 |
| name3 | ccc,ccc | 11,56 |
| name4 | aaa,ccc,ccc,ccc | 87,88,40,99 |
| name5 | aaa,bbb | 1,2 |
SET @parameter:='ccc';
SELECT name, FIND_IN_SET(@parameter, sys) position
FROM mytable1
HAVING position > 0;
| name | position |
|---|---|
| name1 | 3 |
| name2 | 1 |
| name3 | 1 |
| name4 | 2 |
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', FIND_IN_SET(@parameter, sys)), ',', -1) id
FROM mytable1
HAVING id > '';
| id |
|---|
| 5 |
| 22 |
| 11 |
| 88 |
SELECT DISTINCT name, SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', num), ',', -1) id
FROM mytable1
JOIN (SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) numbers
ON SUBSTRING_INDEX(SUBSTRING_INDEX(sys, ',', num), ',', -1) = @parameter
HAVING id NOT IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', FIND_IN_SET(@parameter, sys)), ',', -1)
FROM mytable1);
| name | id |
|---|---|
| name3 | 56 |
| name4 | 40 |
| name4 | 99 |