Search for the first free ip-adress in a range

[PEt0rtje]

Hi there again,

Could some-one help me out with the following problem:

I have a table with ip-adresses in it. Now the ppl that are going to use the database want to be able to determine the first un-used ip-adress in a certain range. That might be in different segments, so the question might be:

Give me the first free ip-adress in the 10.10.100.*
or Give me the first free ip-adress in the 10.10.*.*

And it might also be that there are gaps between the ip-adresses, for instance: ip-adress 10.10.100.18 might be in use, 10.10.100.20 aswell, but 10.10.100.19 might still be free. Then I want 10.10.100.19 to be shown.

If it makes things any easier it is no problem to store the ip's as 010.010.100.019.

Hope this is all the required info.

I really appreciate any help, tnx in advance!!!

 

[JamesLean]

If you can store the IP addresses with leading zeroes as you say then this should work:

SELECT MIN(ipaddress)
FROM table
WHERE ipaddress LIKE '010.010.%'
  AND AddressInUse = 0

--James

 

[PEt0rtje]

A little bit late from my side but:

Tnx for your answer.

I added a 'Status'-field in which i can store the state of the IP-adres (free, in use, whatever) so that way it works. But now I want users to be able to enter the range in which they want to search for the free IP. So some sort of Pop-up box should appear in which they can enter a value...
(Man, I'm a rookie )

Tnx again,

PEt0rtje

 

[JamesLean]

You will need to deal with all user input from your application. SQL Server is a back-end database only, it doesn't have front-end capabilities like Access does.

--James

 

[PEt0rtje]

So that is a concern for later...

Tnx

PEet

 

[PEt0rtje]

Some user-input is allowed now with this stored procedure, by using the Query Analyser:

CREATE PROCEDURE spVrijIpadres @IPADRES varchar (50) AS
SELECT min(ip_Adres) FROM cmdb.Ipadres WHERE
ip_Status = 'Vrij' AND ip_Adres like @IPADRES
GO

Don't know if it will be usable with the application it will be implemented in, but I'm quite content with it

 

[SemperFiDownUnda]

SELECT TOP 1 ip_Adres
FROM cmdb.Ipadres
WHERE ip_Adres BETWEEN @LADD AND @UADD
AND ip_Status = 'Vrij'
ORDER BY ip_Adres

pass in @LADD and @UADD into your SP