select where IP is between 192.168.1.238 and 192.168.1.239

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
LVL 1
SteynskAsked:
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.

ChrisedeboCommented:
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
hasanderCommented:
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

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
Cedric_DCommented:
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
ErnariashCommented:
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
SteynskAuthor Commented:
Thanks
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
Query Syntax

From novice to tech pro — start learning today.