Group by variable substring in MySQL

Question

I have a table that contains multiple fields - let's say FieldA, FieldB etc. and finally Location. The Location field has values such as:

http://192.168.1.10/location?n=5
http://192.168.1.10/location?n=8
http://192.168.15.6/location?n=1
http://192.168.0.9/location?n=11
http://192.168.15.6/location?n=5
http://192.168.0.9/location?n=6
http://192.168.1.10/location?n=2

I need to get the unique values of the IP addresses in the Location field. In other words, from the above example data, I should get

http://192.168.1.10
http://192.168.15.6
http://192.168.0.9

Based on this answer, I am using the following SQL - without much luck

SELECT * FROM `table` WHERE FieldA = 'Example' GROUP BY (SELECT SUBSTRING_INDEX("`table`.Location", "/", 3))

The above gives me just a single record. What am I doing wrong?

Tim Biegeleisen · Accepted Answer · 2021-05-14 11:21:31Z

1

Making judicious use of SUBSTRING_INDEX:

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(Location, '/', 3), '/', -1) AS distinct_ips
FROM yourTable;

Demo

For an explanation on how the above logic works, consider the location value http://192.168.1.10/location?n=5. The inner call to SUBSTRING_INDEX returns http://192.168.1.10, which is everything to the left of the third forward slash. Then, the outer call returns everything to the right of the last forward slash, which leaves us with the IP address.

edited May 14, 2021 at 11:21

answered May 14, 2021 at 10:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chiwda Over a year ago

Super! It works! Thank you! Would you care to elaborate on HOW it works? Need the education. Or perhaps post a link to a "For Dummies" kind of tutorial?

Tim Biegeleisen Over a year ago

@Chiwda I have added an explanation. If you still don't see it, then try exploring more using the demo link I have given in my answer.

Chiwda Over a year ago

Actually, I was wondering why GROUP BY didn't work and why DISTINCT does.

Collectives™ on Stack Overflow

Group by variable substring in MySQL

1 Answer 1

Demo

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related