By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT, txt VARCHAR(255));
INSERT INTO test VALUES
(1,'gruppo-1_text_1'),
(2,'gruppo-12_text_12'),
(3,'gruppo-_text_none'),
(4,'gruppo-44s_text_not_number'),
(5,'gruppo-555 _text_not_number_too'),
(6,'gruppo-1234567890_text_long'),
(6,'gruppo-0000000000_text_long_zerofill');
SELECT * FROM test;
Records: 7 Duplicates: 0 Warnings: 0
| id | txt |
|---|---|
| 1 | gruppo-1_text_1 |
| 2 | gruppo-12_text_12 |
| 3 | gruppo-_text_none |
| 4 | gruppo-44s_text_not_number |
| 5 | gruppo-555 _text_not_number_too |
| 6 | gruppo-1234567890_text_long |
| 6 | gruppo-0000000000_text_long_zerofill |
SELECT id, txt, SUBSTRING(txt FROM 1 + LOCATE('_', txt)), txt REGEXP '^gruppo-\\d+_.*'
FROM test
| id | txt | SUBSTRING(txt FROM 1 + LOCATE('_', txt)) | txt REGEXP '^gruppo-\d+_.*' |
|---|---|---|---|
| 1 | gruppo-1_text_1 | text_1 | 1 |
| 2 | gruppo-12_text_12 | text_12 | 1 |
| 3 | gruppo-_text_none | text_none | 0 |
| 4 | gruppo-44s_text_not_number | text_not_number | 0 |
| 5 | gruppo-555 _text_not_number_too | text_not_number_too | 0 |
| 6 | gruppo-1234567890_text_long | text_long | 1 |
| 6 | gruppo-0000000000_text_long_zerofill | text_long_zerofill | 1 |
SELECT id, txt, SUBSTRING(txt FROM 1 + LOCATE('_', txt))
FROM test
WHERE txt REGEXP '^gruppo-\\d+_.*'
| id | txt | SUBSTRING(txt FROM 1 + LOCATE('_', txt)) |
|---|---|---|
| 1 | gruppo-1_text_1 | text_1 |
| 2 | gruppo-12_text_12 | text_12 |
| 6 | gruppo-1234567890_text_long | text_long |
| 6 | gruppo-0000000000_text_long_zerofill | text_long_zerofill |