REGEXP or Select Replace then GROUP BY

[Szabi Zsoldos]

Hey guys,

I am having some difficulties regarding a select query logic.
Then I have a table with products and it has a column -> submodel with all kind of data in it.

What I want to achieve is to STRIP (with REGEXP?) all the strings starting with ( and ending with ) and then GROUP all X5 for example and instead of 7 X5's I want to have only 1.

Is this possible directly in MySQL ? The ideea is that I cannot clean the column because the data is used in other places also.

[Szabi Zsoldos]

Got it solved :)

SELECT CONCAT(SUBSTRING_INDEX(i.submodel, '(', 1), '', SUBSTRING_INDEX(i.submodel, ')', -1)) AS model FROM products.items as i
    WHERE i.vendor_name LIKE '%(Product_SH)'
GROUP BY 1

[diafol]

My solution:

SELECT SUBSTRING_INDEX(`id`,'(',1) AS groups FROM `table` WHERE `id` LIKE 'x%(%)' GROUP BY groups

[Szabi Zsoldos]

Never have known about this simple LIKE usage in this form! Great input diafol!!!