Member Avatar for 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.

3bcf9a8dac9e8218c88209198e26a7e5

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.

Edited by Szabi Zsoldos
  • 2 Contributors
  • 3 Replies
  • 762 Views
  • 44 Minutes Discussion Span
  • Latest Post Latest Post by Szabi Zsoldos

Dani AI

Generated

Brief expert note: the goal is to normalise the submodel values by removing any parenthesised annotations and then grouping on that cleaned value. ’s string-split approach and @diafol’s LIKE shortcut work for common single-suffix cases. For a more robust and maintainable result, the following options handle more situations, explain tradeoffs, and suggest a performant setup.

Use MySQL 8+ (REGEXP_REPLACE) to remove all parenthesised groups and normalise whitespace/case:

SELECT
  TRIM(LOWER(REGEXP_REPLACE(submodel, '\\s*\\([^)]*\\)', ''))) AS base_model,
  COUNT(*) AS cnt
FROM products.items
WHERE vendor_name LIKE '%Product_SH%'
GROUP BY base_model;

Notes: the regex removes any "(...)" plus any space before it; REGEXP_REPLACE in MySQL 8 replaces all occurrences by default. TRIM and LOWER help with leftover spaces and case differences.

If running older MySQL without REGEXP_REPLACE, a safe single-pass extract uses LOCATE + SUBSTR to cut at the first "(":

SELECT
  TRIM(
    CASE
      WHEN LOCATE('(', submodel) > 0 THEN SUBSTR(submodel, 1, LOCATE('(', submodel)-1)
      ELSE submodel
    END
  ) AS base_model,
  COUNT(*) AS cnt
FROM products.items
GROUP BY base_model;

Caveats and best practices:

  • Functions on the column prevent index use; for large tables add a STORED generated column and index it (MySQL 5.7+ for generated columns, MySQL 8+ if using REGEXP_REPLACE) so grouping can use the index.
  • Nested or malformed parentheses are tricky for regex; repeated removal or application-level cleanup may be needed for deeply nested cases.
  • Collapse multiple internal spaces (e.g., another REGEXP_REPLACE to replace \s{2,} with a single space) and decide on case-sensitivity (use LOWER/UPPER or an explicit collation).
  • Verify results with COUNT(DISTINCT base_model) and sample checks before applying table changes.

These approaches keep the column unchanged (as requested) while making grouping accurate and repeatable; they extend the quick fixes from and @diafol into more robust, production-safe patterns.

Member Avatar for 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

f3a468a094655ec9d09f858762daffea

Edited by Szabi Zsoldos
Member Avatar for Member #120589

My solution:

SELECT SUBSTRING_INDEX(`id`,'(',1) AS groups FROM `table` WHERE `id` LIKE 'x%(%)' GROUP BY groups
Edited by Member #120589
Member Avatar for Szabi Zsoldos

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

Edited by Szabi Zsoldos
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.