Question 113102 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql query GROUP BY trimed data column

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search