mysql query GROUP BY trimed data column-CodePudding

i'm working with MYSQL, and have a problem with group by column that data has to be trimed first. here is my table:

src	dst
source one	some_character1/dst_one-random_value1
source one	some_character1/dst_one-random_value2
source one	some_character2/dst_two-random_value3
source two	some_character4/dst_two-random_value1
source two	some_character4/dst_three-random_value2
source two	some_character2/dst_three-random_value7

i want to group by this table into like this :

dst_group_by
dst_one
dst_two
dst_three

the dst value has 3 section.

The first section is seperated by '/', and the last section is seperated by '-'.

First section and last section character length is random, and i can determined it. I only want to group by the middle section.

Is there any effective query to do that ?

Thanks before.

CodePudding user response：

Use SUBSTRING_INDEX to get the between value you want to GROUP BY:

SELECT a.src, a.dst_group_by
FROM (SELECT src, SUBSTRING_INDEX(SUBSTRING_INDEX(dst, '/', -1), '-', 1) AS dst_group_by
      FROM sample) a
GROUP BY a.src, a.dst_group_by

Result:

| src        | dst_group_by  |
|------------|---------------|
| source one | dst_one       |
| source one | dst_two       |
| source two | dst_two       |
| source two | dst_three     |

Or if you want to return the DISTINCT values:

SELECT DISTINCT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(dst, '/', -1), '-', 1) AS dst_group_by
FROM sample

Result:

| dst_group_by  |
|---------------|
| dst_one       |
| dst_two       |
| dst_three     |

Fiddle here.

CodePudding user response：

MySQL has substring function, you can define like that

select src,substring(dst,start_position,substring_length) 
group by substring(dst,start_position,substring_length)

I don't know if the position is indexed by 0 or by 1 but the idea is like:

select src, substring(dst,16,6) 
from table_name 
group by substring(dst,16,6)

I hope this can help you