Parsing an IP address field string

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parsing an IP address field string

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-30 : 07:52:33

Rene Lariviere writes "I have a table that has two fields, IPAddress and SubnetMask.

How do I parse the IPAddress field based on the SubnetMask field to find the subnet? For example, the IPAddress is 10.101.14.151 and the SubnetMask is 255.255.255.0. How do I get 10.101.14.0 out of it?

The problem is the varaible length of the IPAddress!

I use SQL 2000 on Windows 2000

Thanks in advance!"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-30 : 08:47:43

If you have SQL2K you can create a UDF...


CREATE FUNCTION SubNetAddress
(
@IPAddress VARCHAR(15),
@SubNetMask VARCHAR(15)
)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @SubNetAddress VARCHAR(15)
SET @SubNetAddress = ''
WHILE CHARINDEX('.', @IPAddress) > 0
BEGIN
	SET @SubNetAddress = @SubNetAddress + CASE WHEN LEN(@SubNetAddress) > 0 THEN '.' ELSE '' END + CAST(CAST(SUBSTRING(@IPAddress,0,CHARINDEX('.', @IPAddress)) AS INT) & CAST(SUBSTRING(@SubNetMask,0,CHARINDEX('.', @SubNetMask)) AS INT) AS VARCHAR(3))
	SET @IPAddress = RIGHT(@IPAddress, LEN(@IPAddress) - CHARINDEX('.', @IPAddress))
	SET @SubNetMask = RIGHT(@SubNetMask, LEN(@SubNetMask) - CHARINDEX('.', @SubNetMask))
END
RETURN @SubNetAddress + '.' + CAST(CAST(@IPAddress AS INT) & CAST(@SubNetMask AS INT) AS VARCHAR(3))
END

DavidM

"SQL-3 is an abomination.."

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-07-30 : 09:58:07

Or you can use the RobVolk Parsename Hack
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22081

Damian

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-30 : 14:42:51

My $0.02:

declare @a varchar(15), @b varchar(15)
set @a='10.101.14.151'
set @b='255.255.255.0'

select

case
when substring(@b,5,1)='0' then left(@a,charindex('.',@a))+'0.0.0'
else
case
when
substring(@b,9,1)='0' then left(@a,charindex('.',@a,1+charindex('.',@a)))+'0.0'
else
left(@a,charindex('.',@a,len(@a)-charindex('.',reverse(@a))))+'0'
end
end

- Vit

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-30 : 19:37:02

Something like:

SELECT CAST(CAST(PARSENAME(IP,4) as tinyint) & CAST(PARSENAME(SubNet, 4) AS tinyint) as varchar) + '.' +
CAST(CAST(PARSENAME(IP,3) as tinyint) & CAST(PARSENAME(SubNet, 3) AS tinyint) as varchar) + '.' +
CAST(CAST(PARSENAME(IP,2) as tinyint) & CAST(PARSENAME(SubNet, 2) AS tinyint) as varchar) + '.' +
CAST(CAST(PARSENAME(IP,1) as tinyint) & CAST(PARSENAME(SubNet, 1) AS tinyint) as varchar)
FROM myTable

This is the parsename "hack" (AHEM!!!) that Damian mentioned.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-07-30 : 19:43:19

quote:
Originally posted by robvolk

"hack" (AHEM!!!)

No, that was more of a throat clearing than a hack

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources