Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
1fred
Posting Yak Master
158 Posts |
 Posted - 2002-12-11 : 10:53:38
|
| In my table, the ip address are varchar datatype. How could I make this query works? SELECT *FROM CVisitTotalTWHERE (IP BETWEEN '216.130.212.0' AND '216.130.212.255') |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-11 : 11:13:15
|
| doesn't it work already?You would have problems if one of the others is not 3 chars though.tryparsename right('000' + (@addr,4),3)+ right('000' + (@addr,3),3)+ right('000' + (@addr,2),3)+ right('000' + (@addr,1),3)(Kudos to robvolk (I think it was) for pointing out this cludge)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 12/11/2002 12:29:13 |
 |
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-12-11 : 11:43:30
|
| Ok thanks, but my brain started to think and the easier way might be select * from CVisitTotalT where ip like '216.130.212.%' |
|
|
|
Argyle
Yak Posting Veteran
53 Posts |
Posted - 2002-12-11 : 11:57:29
|
| If you want some more advanced options and you have SQL 2000 you can create a second column (or convert the existing one) and store the ip as a bigint.Made some functions for that once (migth not be optimal but they work :D ). Pasting them in below:-----------------------------CREATE FUNCTION dbo.fn_big2ip (@i BIGINT)/* Return a bigint as an IP (x.x.x.x) */RETURNS VARCHAR(15) AS BEGIN RETURN CAST(@i/(256*256*256) AS VARCHAR(3)) + '.' + CAST(@i%(256*256*256)/(256*256) AS VARCHAR(3)) + '.' + CAST(@i%(256*256)/256 AS VARCHAR(3)) + '.' + CAST(@i%(256) AS VARCHAR(3))END-----------------------------CREATE FUNCTION dbo.fn_ip2big (@s VARCHAR(15))/* Return an IP (x.x.x.x) as a bigint */RETURNS BIGINT AS BEGIN RETURN CAST(SUBSTRING(@s, 1, CHARINDEX('.',@s, 1)-1) AS BIGINT)*(256*256*256) + CAST(SUBSTRING(@s, CHARINDEX('.',@s, 1)+1, CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)-CHARINDEX('.',@s, 1)-1) AS BIGINT) * (256*256) + CAST(SUBSTRING(@s, CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)+1, CHARINDEX('.',@s,CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)+1)-CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)-1) AS BIGINT) * (256) + CAST(SUBSTRING(@s, CHARINDEX('.',@s,CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)+1)+1, len(@s)-CHARINDEX('.',@s,CHARINDEX('.',@s,CHARINDEX('.',@s, 1)+1)+1)) AS BIGINT)END-----------------------------CREATE TABLE [iptable] ( [ipaddress] [bigint] NOT NULL ) ON [PRIMARY]GO-----------------------------insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.0'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.1'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.2'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.3'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.4'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.5'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.6'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.7'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.8'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.9'))insert into iptable (ipaddress) values (dbo.fn_ip2big('192.168.0.10'))-----------------------------/* Find IP's withing a range */select dbo.fn_big2ip(ipaddress) as ipaddress from iptable where ipaddress between dbo.fn_ip2big('192.168.0.5') and dbo.fn_ip2big('192.168.0.10')-----------------------------/* Find all IP's on a specific network when you know an IP and the subnet mask (using bitwise compare) *//* This example will return 192.168.0.0 to 192.168.0.7 */select dbo.fn_big2ip(ipaddress) as ipaddress from iptable where (dbo.fn_ip2big('192.168.0.5') & dbo.fn_ip2big('255.255.255.248')) = (ipaddress & dbo.fn_ip2big('255.255.255.248'))-----------------------------With other types of select statments you can get a list of available IP's on your specific networks if you have a table contaning all IPs you currently use etc. The datatype bigint is used since 32 bits are required but an int in SQL Server only covers 31 since it's signed. bigint covers 63. IPv6 will not be covered since it uses 128 bits if I remember correctly./Argyle |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
|
|
|
|
Selecting IP