MySQL GROUP from Regex?

Question

I have the following request

SELECT Count(*) as Total_Count, Col1 FROM Table1 GROUP BY Col1 ORDER BY Total_Count DESC; 

I want to zoom in on Col1. Data in Col1 has the following format:

 text-abc1 txt4-abcde22 tex6-abc2 text4-imp4 text-efg1 txt-efg43 

I want to be able to group it

 After the first `-`, any first three/four/five characters match 

In this example, if we match the first 3 characters. The output will be:

 Total_Count Col1 3 abc 1 imp 2 efg 

Any other way to achieve this?

Answer 1

You may not need a regular expression, just string operations. For three characters:

 SELECT count(*) AS Total_Count, SUBSTRING(Col1 FROM POSITION('-' in Col1)+1 FOR 3) AS Col1_zoomed FROM Table1 GROUP BY Col1_zoomed ORDER BY Total_Count DESC 

Answer 2

 select substring(substring_index(col1,'-',-1),1,3) as grp, count(*) as total from table group by grp 

Answer 3

This should do what you want.

 SELECT Count(*) as Total_Count, SUBSTRING(Col1, 1, 3) FROM Table1 GROUP BY SUBSTRING(Col1, 1, 3) ORDER BY Total_Count DESC;