MS SQL - Query Analyzer by IP range

I need to run reports from our database that stores IP addresses when a software license is used. So, I need to know how to run SQL queries to retrieve certain IP addresses from specified ranges. For example:

192.168.1.0 - 192.168.2.255

192.168.3.11 - 192.168.7.128

10.10.10.8 - 10.10.12.255

What commands would help me accomplish pulling all IP addresses in a range. I know I would have to run multiple reports for each range. I tried using the "between" function and "less than/greater than" functions, but I received mixed results. For example:

SELECT * FROM testtable WHERE ip LIKE '192.168.[1-2].[0-255]'

I also tried -

SELECT * FROM testtable WHERE ip>'192.168.1.0' and ip<'192.168.2.255'

Any help would be greatly appreciated! Thanks in advance!Parse your IP addresses into four separate columns. That would make it easy to search or filter by ranges.|||blindman, thank you for the feedback... however, the original DB configured the IP address as it's own field... at this point, there is over 10million or so records, so modifying the DB would be time consuming... any other thoughts would be greatly appreciated...|||Create a function called IP2DWord or so. Its pretty straigtforward. First octet * 256^3 + second octet * 256^2 + third octet*256 + fourth octet. If you use this function on both starting and ending address you can easily select ranges.|||Define: "time consuming"? ;)
There have been a number of questions about IP addresses in a range - I suggest you search the forum www.dbforums.com/search.php and there were some very good suggestions around and about (see above ;)).|||roac, i looked into your suggestion and it seems to be the way to go... thnx all for the feedback...|||If you parse the IP address on the fly using a function, then you can toss any indexing out the window. On 10,000,000+ records don't expect a great response time.
You asked for advice, and I gave it. You are FAR better off parsing the IP Address into four separate columns in your table. You can index them, and you only need to parse each address once. If you absolutely have to have the IP Address as a single column in your table, then keep it and create four additional columns in the table that you populate automatically using insert/update triggers.
You can do this right, or you can do this wrong. You can take shortcuts now, or you can save programming, debugging, and execution time over the life of the application.
'Nuff said.|||For reporting purposes it is required for the IP address to be in 1 column. I will look into separating each IP segment into it's on column, as I agree there's more flexibility when running reports specifically for IP ranges.|||For reporting purposes it is required for the IP address to be in 1 column.Reporting purposes do not require the data to be in one column. A view, sproc, or SQL statement can easily combine the components for reporting.