Stored Procedure to validate format of IP address

Hi Experts,

I need to write a stored procedure to take an IP address and check that it is in the correct format (i.e. 4 number separated by dots that are between 0 and 255). I'm not sure if there are any other validations that need to be done on an IP address but that's the most basic check.

Really appreciate your help.
SWD
SurreyWebDesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patricka_0377Commented:
I would look at using regular expressions
http://www.sqlteam.com/article/regular-expressions-in-t-sql

i think I have used this in the past with good results
http://www.codeproject.com/KB/mcpp/xpregex.aspx
0
SurreyWebDesignerAuthor Commented:
Hmmmm ... I'd rather just keep it in a stored procedure (if only because I already have one which is doing various other checks and like to just pop this straight in).

This looks a little too complicated for me I'm afraid (I'm not a regex expert at all).

Thanks for the suggestion.
SWD
0
Anthony PerkinsCommented:
This should get you going:

declare @IP varchar(15),
            @IPTest varchar(15),
            @Counter tinyint

Set @IP = '101.102.103.104'

Set @Counter = 4
While @Counter > 0
      Begin
            Set @IPTest = PARSENAME(@IP, @Counter)
            If ISNUMERIC(@IPTest) = 1
                  Begin
                        If @IPTest BETWEEN 0 And 255
                              Begin
                                    Set @Counter = @Counter - 1
                              End
                        Else
                              Begin
                                    Break
                              End
                  End
            Else
                  Begin
                        Break
                  End
                        
      End

Select      CASE @Counter
                  WHEN 0 THEN 'Success'
                  ELSE 'Error in node ' + CAST(5 - @Counter as varchar(1))
            END
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SurreyWebDesignerAuthor Commented:
Hi acperkins - thanks for this, it's very nearly perfect! A few problems I'm having:

1. I've modified your code to fit in with my current SP but it just hangs when I run it - any ideas??
2. From the testing I've done so far, if you enter too many digits in the last node it doesn't throw an error.

Many thanks and apologies if I'm being dumb with the code below! (always possible!!)

Cheers
SWD
declare @IP varchar(15),
@IPTest varchar(15),
@Counter tinyint,
@FailureDetails varchar(300),
@Status varchar(50)
 
Set @IP = '101.102.103.104'
 
Set @Counter = 4
While @Counter > 0
      Begin
            Set @IPTest = PARSENAME(@IP, @Counter)
            If ISNUMERIC(@IPTest) = 1
                  Begin
                        If @IPTest BETWEEN 0 And 255
                              Begin
                                    Set @Counter = @Counter - 1
                              End
                        Else
                              Begin
                                 SET @Status = 'FAILURE'
			     SET @FailureDetails = @FailureDetails + 'Error in IP; '
                              End
                  End
            Else
                  Begin
                     	SET @Status = 'FAILURE'
			SET @FailureDetails = @FailureDetails + 'Error in IP; '
                  End
                        
      End
 
	If(@Counter<>0)
		BEGIN
			SET @Status = 'FAILURE'
			SET @FailureDetails = @FailureDetails + 'Error in IP node ' + CAST(5 - @Counter as varchar(1))
		END

Open in new window

0
Anthony PerkinsCommented:
Try it this way:

Declare @IP varchar(50),
		@IPTest varchar(50),
		@Counter tinyint,
		@FailureDetails varchar(300),
		@Status varchar(50)
 
Set @IP = '101.102.103.1041234564'
 
Set @Counter = 4
While @Counter > 0
	Begin
		Set @IPTest = PARSENAME(@IP, @Counter)
		If ISNUMERIC(@IPTest) = 1 And LEN(@IPTest) <= 3
			Begin
				If CAST(@IPTest As smallint) BETWEEN 0 And 255
					Begin
						Set @Counter = @Counter - 1
					End
				Else
					Begin
					--			SET @Status = 'FAILURE'
					--			SET @FailureDetails = @FailureDetails + 'Error in IP; '
						BREAK
					End
			End
		Else
			Begin
--				SET @Status = 'FAILURE'
--				SET @FailureDetails = @FailureDetails + 'Error in IP; '
				BREAK
			End
 
	End
 
If @Counter <> 0
	BEGIN
		Select	@Status = 'FAILURE',
				@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest
	END

Open in new window

0
Anthony PerkinsCommented:
>>1. I've modified your code to fit in with my current SP but it just hangs when I run it - any ideas??<<
You omitted exiting out of the loop when it failed hence a never ending loop.

>>2. From the testing I've done so far, if you enter too many digits in the last node it doesn't throw an error.<<
You are right.  I made the assumption that the max number of characters was 15.  I have changed it to 50, but it should be modified to whatever size you use.

This code might be clearer:
Declare @IP varchar(50),
		@IPTest varchar(50),
		@Counter tinyint,
		@FailureDetails varchar(300),
		@Status varchar(50)
 
Set @IP = '101.102.103.1041234564'
 
Set @Counter = 4
While @Counter > 0
	Begin
		Set @IPTest = PARSENAME(@IP, @Counter)
		If ISNUMERIC(@IPTest) = 1 And LEN(@IPTest) <= 3
			Begin
				If CAST(@IPTest As smallint) BETWEEN 0 And 255
					Begin
						Set @Counter = @Counter - 1
					End
				Else
					Begin
						-- SET @Status = 'FAILURE'
						-- SET @FailureDetails = @FailureDetails + 'Error in IP; '
						BREAK
					End
			End
		Else
			Begin
				-- SET @Status = 'FAILURE'
				-- SET @FailureDetails = @FailureDetails + 'Error in IP; '
				BREAK
			End
 
	End
 
If @Counter <> 0
	BEGIN
		Select	@Status = 'FAILURE',
				@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest
	END

Open in new window

0
SurreyWebDesignerAuthor Commented:
Hi,

Brilliant - thanks very much, we're almost there now I feel!

One thing that doesn't seem to be working is if I have less than 4 nodes in the IP address it isn't returning anything for @FailureDetails. And I've uncommented the necessary lines.

Thanks again for your help - I'm a bit of a newbie on this stuff so apologies if I'm missing something.

Cheers
SWD
0
Anthony PerkinsCommented:
Replace:
@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + @IPTest

With:
@FailureDetails = 'Error in IP node: ' + CAST(5 - @Counter as varchar(1)) + ', Value: ' + ISNULL(@IPTest, '')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SurreyWebDesignerAuthor Commented:
Spot on! Thanks very much for your expert help!

Cheers
SWD
0
sqlsaintCommented:
Here is a function - Cut and Paste!!!

drop function IsIPAddress
go
create function IsIPAddress (@String varchar(100)) returns bit as
begin

declare  @Result      int,
      @Pos      int,
      @Dots      int,
      @Val      varchar(3)
      
select      @Result      = 1,
      @Pos      = 0,
      @Dots      = 0,
      @Val      = ''

while @Pos < len(@String)
   begin
      select @Pos = @Pos + 1
      if substring(@String, @Pos, 1) = '.'
         select @Dots = @Dots + 1
      else
         begin
            select @Val = @Val + substring(@String, @Pos, 1)
            
            -- check the segment length
            if len(@Val) > 3
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment is a number
            if isnumeric(@Val) <> 1
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment number is less then 255
            if convert(int, @Val) > 255
               begin
                  select @Result = 0
                  return @Result
               end
         end
   end

-- Count the segments "Dots"
if @Dots <> 3
   begin
      select @Result = 0
      return @Result
   end

return @Result

      
end
go

set nocount on

select dbo.IsIPAddress('1.2.3.4')
select dbo.IsIPAddress('111.112.113.114')
select dbo.IsIPAddress('266.2.3.4')
select dbo.IsIPAddress('a1.2.3.4')
select dbo.IsIPAddress('1.2.3.4.5')
0
sqlsaintCommented:
Whoops - test first - use this one...

drop function IsIPAddress
go
create function IsIPAddress (@String varchar(100)) returns bit as
begin

declare  @Result      int,
      @Pos      int,
      @Dots      int,
      @Val      varchar(3)
      
select      @Result      = 1,
      @Pos      = 0,
      @Dots      = 0,
      @Val      = ''

while @Pos < len(@String)
   begin
      select @Pos = @Pos + 1
      if substring(@String, @Pos, 1) = '.'
         select @Dots = @Dots + 1
      else
         begin
            select @Val = @Val + substring(@String, @Pos, 1)
            
            -- check the segment length
            if len(@Val) > 3
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment is a number
            if isnumeric(@Val) <> 1
               begin
                  select @Result = 0
                  return @Result
               end

            -- check that the segment number is less then 255
            if convert(int, @Val) > 255
               begin
                  select @Result = 0
                  return @Result
               end
         end
   end

-- Count the segments "Dots"
if @Dots <> 3
   begin
      select @Result = 0
      return @Result
   end

-- Check to make sure that the last charcter is not a dot
if substring(@String, len(@String), 1) = '.'
   begin
      select @Result = 0
      return @Result
   end


return @Result

      
end
go

set nocount on

-- Should Pass
select dbo.IsIPAddress('1.2.3.4')
select dbo.IsIPAddress('111.112.113.114')

-- Should Fail
select dbo.IsIPAddress('a1.2.3.4')
select dbo.IsIPAddress('1.2.3')
select dbo.IsIPAddress('1.2.3.')
select dbo.IsIPAddress('1.2.3.4.5')
select dbo.IsIPAddress('266.2.3.4')
0
Anthony PerkinsCommented:
You do realize this thread is 2 years old, right?

But surely you jest.  Why would you want to loop through each character?  That has to be a dog.  There are far better ways of retrieving delimited strings.  Besides ISNUMERIC() (yes, I realize the accepted solution also uses it) is a lousy way to test for integers.  You are better of using something like NOT LIKE '%[^0-9]%'  You can see for yourself with any of the following:
$1.2.3.4
-1.2.3.4
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.