Group by variable substring in MySQL

user_989468 0
Viewed 35

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?

substring group-by mysql
1 Answers

Making judicious use of SUBSTRING_INDEX:

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

screen capture from demo link below

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.

user_18632290
Related