How to update an IP Address Address with Leading zeros

Question

Post reply

How to update an IP Address Address with Leading zeros

marcus.mcdowell

Old Hand

Points: 370
More actions
July 27, 2009 at 8:48 am

#140178

Importing from a network management tool, the IP address of a workstation is given as:
010.000.123.094
for example. I've written the following code as a trigger on the table where the data is imported and it does the job, but is there a better way of modifying the record? I searched online for quite some time and didn't see anything which would do what I wanted, so this was my first stab (I know a bit of sql, but am far from being an expert):
create trigger [dbo].[i_u_usr_workstation] on [dbo].[usr_workstation] for insert, update as
begin
if update(usr_ipaddress)
begin
declare @GUID uniqueidentifier
declare @ipAddress varchar(20)
declare @newIP varchar(20)
declare @intLeadingZero int
declare @intLength int
select @GUID=cf_guid, @ipaddress=usr_ipaddress from inserted
select @intLEadingZero = 0
if @intLeadingZero = 1
begin
select @intLength = len(@ipAddress)
select @ipAddress = right(@ipAddress, @intlength - 1)
end
select @intLeadingZero = charindex('0',@ipAddress)
if @intLeadingZero = 1
begin
select @intLength = len(@ipAddress)
select @ipAddress = right(@ipAddress, @intlength - 1)
end
select @intLeadingZero = charindex('.0',@ipAddress)
while @intLeadingZero > 0
begin
if substring(@ipaddress ,@intleadingzero, 3) <> '0.0' and substring(@ipAddress, @intLeadingZero, 3) <> '.0.'
begin
select @intLength = len(@ipaddress)
select @newIP = left(@ipAddress, @intLeadingZero)
select @ipAddress = @newIP + right(@ipAddress, @intlength - @intLeadingZero - 1)
select @intLeadingZero = charindex('.0', @ipAddress)
end
else
select @intLeadingZero = charindex('.0', @ipAddress, @intLeadingZero + 1)
end
update usr_workstation set usr_ipaddress = @ipaddress where cf_guid = @GUID
end
end

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Can you explain what you are trying to do? Are you trying to add leading zeros or remove leading zeros?

marcus.mcdowell Old Hand Points: 370 More actions · Answer 2

Apologies. Just getting the hang of putting code in here!

Trying to get the value 010.000.123.094 to normal IP address format: 10.0.123.94. The only way I could think of doing it was to run through the string (it's stored as varchar(20)) and remove leading zeros from each IP octet.

Lowell SSC Guru Points: 323518 More actions · Answer 3

only way i could think of was to use parsename and do a double convert from int back to varchar:

declare @ipAddress varchar(20)

SET @ipAddress='010.000.123.094'

SELECT

CONVERT(varchar(3),convert(int,parsename(@ipAddress,4))) + '.' +

CONVERT(varchar(3),convert(int,parsename(@ipAddress,3))) + '.' +

CONVERT(varchar(3),convert(int,parsename(@ipAddress,2))) + '.' +

CONVERT(varchar(3),convert(int,parsename(@ipAddress,1)))

--results:

10.0.123.94

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

marcus.mcdowell Old Hand Points: 370 More actions · Answer 4

Perfect! Works a treat and is much more efficient codewise. I doff my hat to you!