?
Solved

select where IP is between 192.168.1.238 and 192.168.1.239

Posted on 2008-11-10
5
Medium Priority
?
3,244 Views
Last Modified: 2012-05-05
Hello Expert,

I am a little new to SQL. But I need a query that selects only the records in which the field value is between 192.168.1.238 and 192.168.1.239.

Can someone please make the select statement?

Thanks,

Steynsk
0
Comment
Question by:Steynsk
5 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 22921302
select * from table_name where ip_address in ('192.168.1.238', '192.168.1.239')

you will need to replace table_name with the name of the table you wish to select from and ip_address with the name of the field within that table that contains the ip addresses you wish to query.
0
 
LVL 1

Accepted Solution

by:
hasander earned 1000 total points
ID: 22921304
In this case you would be able to use:


SELECT * FROM yourTablename WHERE IP BETWEEN '192.168.1.238' and '192.168.1.239'

Open in new window

0
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22922138
this is true when IP is of varchar type. If it use numeric representation, then you might use
SELECT * FROM yourTablename WHERE IP BETWEEN 3232236014 and 3232236015

you can convert, for example, at http://tools.devshed.com/webmaster-tools/ip-convert/
0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22922987
You can use a function converts string-based IP addresses to their numeric equivalents so that it is easy to do arithmatic on them such as find an IP address that is within a range:
SELECT * FROM yourTablename
WHERE dbo.IPStringToNumber(IP) BETWEEN dbo.IPStringToNumber( '192.168.1.238') and dbo.IPStringToNumber( '192.168.1.239')  
See code below that is from this site: http://www.sqlservercentral.com/scripts/Miscellaneous/30943/

SELECT * FROM yourTablename 
WHERE dbo.IPStringToNumber(IP) BETWEEN dbo.IPStringToNumber( '192.168.1.238') and  dbo.IPStringToNumber( '192.168.1.239') 
 
CREATE FUNCTION dbo.IPStringToNumber( @vcIPAddress varchar(15))
/**************************************************************************
DESCRIPTION: Returns Numeric IP, otherwise returns null
 
PARAMETERS:
		@vcIPAddress	- The string containing a valid IP
		
RETURNS:	IP converted to bigint or null if not a valid IP
		
USAGE:         SELECT  dbo.IPStringToNumber( '10.255.255.255')
		
 
AUTHOR:	Karen Gayda
 
DATE: 	06/11/2003
 
MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------
 
***************************************************************************/
 
	RETURNS bigint
AS
BEGIN
	DECLARE	
		@biOctetA 	bigint,
		@biOctetB	bigint,
		@biOctetC	bigint,
		@biOctetD	bigint,
		@biIP	    	bigint
 
	DECLARE @tblArray TABLE 
	   (
		OctetID		smallint,  		--Array index
	   	Octet		bigint		   	--Array element contents
	   )
 
	--split the IP string and insert each octet into a table row
	INSERT INTO @tblArray
	SELECT ElementID, Convert(bigint,Element) FROM dbo.Split(@vcIPAddress, '.')
	
	--check that there are four octets and that they are within valid ranges
	IF (SELECT COUNT(*) FROM @tblArray WHERE Octet BETWEEN 0 AND 255) = 4
	BEGIN
		SET @biOctetA = (SELECT (Octet * 256 * 256 * 256) FROM @tblArray WHERE OctetID = 1)
		SET @biOctetB = (SELECT (Octet * 256 * 256 ) FROM @tblArray WHERE OctetID = 2)
		SET @biOctetC = (SELECT (Octet * 256 ) FROM @tblArray WHERE OctetID = 3)
		SET @biOctetD = (SELECT (Octet) FROM @tblArray WHERE OctetID = 4)
		SET @biIP = @biOctetA + @biOctetB + @biOctetC + @biOctetD
	END
		
	RETURN(@biIP)
END
 
 
IF exists (SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[Split]') 
	AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO
 
 
GO
CREATE FUNCTION dbo.Split (	@vcDelimitedString 		varchar(8000),
				@vcDelimiter			varchar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
		delimiter points.  Returns the individual items as a table data
		type with the ElementID field as the array index and the Element
		field as the data
 
PARAMETERS:
		@vcDelimitedString		- The string to be split
		@vcDelimiter			- String containing the delimiter where
							delimited string should be split
 
RETURNS:
		Table data type containing array of strings that were split with
		the delimiters removed from the source string
 
USAGE:
		SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
 
AUTHOR:	Karen Gayda
 
DATE: 	05/31/2001
 
MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------
 
***************************************************************************/
RETURNS @tblArray TABLE 
   (
	ElementID	smallint	IDENTITY(1,1),  --Array index
   	Element		varchar(1000)			--Array element contents
   )
AS
BEGIN
 
	DECLARE 
	@siIndex					smallint,
	@siStart					smallint,
	@siDelSize					smallint
 
 
	SET @siDelSize	= LEN(@vcDelimiter)
	--loop through source string and add elements to destination table array
	WHILE LEN(@vcDelimitedString) > 0
	BEGIN
		SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
		IF @siIndex = 0
		BEGIN
			INSERT INTO @tblArray VALUES(@vcDelimitedString)
			BREAK
		END
		ELSE
		BEGIN
			INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
			SET @siStart = @siIndex + @siDelSize
			SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
		END
	END
	
	RETURN
END
GO

Open in new window

0
 
LVL 1

Author Closing Comment

by:Steynsk
ID: 31515015
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question