MySQL GROUP by Regex？ [英] MySQL GROUP by Regex?

问题描述

我有以下查询

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

我想放大Col1。 Col1中的数据格式如下：

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

在此示例中，如果我们匹配前3个字符。输出将为：

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?

推荐答案

你可能不需要一个正则表达式，只是字符串操作。对于三个字符：

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

这篇关于MySQL GROUP by Regex？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！