SELECT rows GROUP BY a certain regex format

Question

I have a table in my MySQL DB which has a column product_name and it has rows like

KK004A
KK004B
KK004C
KK007
N01002X/G
N01005X/G
N01005Y/G
N01005Z/G
002A
002B

I want to GROUP BY so that it returns:

KK004A
KK007
N01002X/G
N01005X/G
002A

That is, it groups by Regex [A-Z]*\d+

How can I do this? I'm open to non-Regex ways

Thanks

score 2 · Answer 1 · edited May 23 '17 at 12:27

This is what you can do using the technique from Finding number position in string

SELECT 
  val
  from test
  group by 
  SUBSTRING( 
    val,1,(LENGTH(val)+1)-LEAST (
      if (Locate('0',reverse(val)) >0,Locate('0',reverse(val)),999),
      if (Locate('1',reverse(val)) >0,Locate('1',reverse(val)),999),
      if (Locate('2',reverse(val)) >0,Locate('2',reverse(val)),999),
      if (Locate('3',reverse(val)) >0,Locate('3',reverse(val)),999),
      if (Locate('4',reverse(val)) >0,Locate('4',reverse(val)),999),
      if (Locate('5',reverse(val)) >0,Locate('5',reverse(val)),999),
      if (Locate('6',reverse(val)) >0,Locate('6',reverse(val)),999),
      if (Locate('7',reverse(val)) >0,Locate('7',reverse(val)),999),
      if (Locate('8',reverse(val)) >0,Locate('8',reverse(val)),999),
      if (Locate('9',reverse(val)) >0,Locate('9',reverse(val)),999)
    )
  )
;

DEMO

You need to change table and column name in the above query.

SELECT rows GROUP BY a certain regex format

1 Answers1