MySQL GROUP by Regex?

Question

I have the following query

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

I want to zoom in on Col1. The data in Col1 are in the following format:

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

I want to be able to group it by

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

In this example, if we match with first 3 characters. Output will be:

Total_Count   Col1
3             abc
1             imp
2             efg

Any other way to achieve this?

Solution 1

You might not need a regex, 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

Solution 2

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

Solution 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;

Comments

[theking963]

SUBSTRING(Col1, 1, 3) won't work in this case as I need to match characters after the -. Before the - there could be any number of characters.

[Adrian Cornish]

I misread your question - I thought you wanted the first 3. Ian's answer is better then

[theking963]

This is the right one. Thanks for it. But abcd-1234 needs to be Col1. Please change it so I can accept it :).

[Wiseguy]

@daking963 As if this answer doesn't help you when one thing is spelled differently? Picky, picky! :-p

[theking963]

@Wiseguy It's not about being picky. It's for the correctness of the answer and a future reference for others. I am sure they can figure it out that instead of the string it should be the column name. It's just a typo, Ian can change it easily. To improve the overall quality of the site, in my opinion, these little things matter a lot!

[Ian Clelland]

Done :) Sorry, it was my sample data from shell-testing