By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

How to create a GROUP BY for use with ip address range?

P: 5
Dudes,

How can I make a GROUP BY function into an SQL instruction, to count how much machines by ip address range (until the 3rd range)?

Ex: (Existent registers for a field called ip_addr)

172.17.17.1
172.17.17.2
172.17.17.3
172.18.196.12
172.18.196.22
172.22.3.1

Range 172.17.17: 3 machines
Range 172.18.196: 2 machines
Range 172.22.3: 1 machine
Mar 14 '07 #1
Share this Question
Share on Google+
3 Replies


P: 2
Dudes,

How can I make a GROUP BY function into an SQL instruction, to count how much machines by ip address range (until the 3rd range)?

Ex: (Existent registers for a field called ip_addr)

172.17.17.1
172.17.17.2
172.17.17.3
172.18.196.12
172.18.196.22
172.22.3.1

Range 172.17.17: 3 machines
Range 172.18.196: 2 machines
Range 172.22.3: 1 machine
You can (in ver.8) group by
SUBSTR(ip_addr,1,
LOCATE('.',ip_addr,
LOCATE('.',ip_addr,LOCATE('.',ip_addr)+1)+1)-1)
because
SELECT SUBSTR('172.18.196.12',1,
LOCATE('.','172.18.196.12',
LOCATE('.','172.18.196.12',LOCATE('.','172.18.196. 12')+1)+1)-1)
FROM SYSIBM.SYSDUMMY1
is 172.18.196

regards,
poddus
Mar 15 '07 #2

P: 2
you can (in ver.8) group by
SUBSTR(ip_addr,1,
LOCATE('.',ip_addr,
LOCATE('.',ip_addr,LOCATE('.',ip_addr)+1)+1)-1)
becase FROM SYSIBM.SYSDUMMY1
SELECT SUBSTR('172.18.196.12',1,
LOCATE('.','172.18.196.12',
LOCATE('.','172.18.196.12',LOCATE('.','172.18.196. 12')+1)+1)-1)
from sysibm.sysdummy1 is 172.18.196
regards,
poddus
Mar 15 '07 #3

P: 5
Ok, thanks a lot!
Mar 16 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.