SQL Server: Store and Retrieve IP Address

We can store IP addresses in SQL Server in a varchar column. However to retrieve IP address for a specific range, we need to split each part and compare it.

Consider the following table:

Suppose we want to retrieve all the IP addresses that fall in the range from 192.168.120.120
to 192.168.200.255. The following query will retrieve these IP addresses:

Here’s the same query for you to try out:

declare @from_ip varchar(20), @to_ip varchar(20)

select @from_ip='192.168.120.120',@to_ip='192.168.200.255'

select * from @t
where
parsename(Ip_address,4)*1>= parsename(@from_ip ,4)*1 and
parsename(Ip_address,4)*1<= parsename(@to_ip ,4)*1
and
parsename(Ip_address,3)*1>= parsename(@from_ip ,3)*1 and
parsename(Ip_address,3)*1<= parsename(@to_ip ,3)*1
and
parsename(Ip_address,2)*1>= parsename(@from_ip ,2)*1 and
parsename(Ip_address,2)*1<= parsename(@to_ip ,2)*1
and
parsename(Ip_address,1)*1>= parsename(@from_ip ,1)*1 and
parsename(Ip_address,1)*1<= parsename(@to_ip ,1)*1

In the query shown above, the Parsename function is used split IP addresses based on a dot (.) . As the IP address is stored in a varchar data type, we need to convert to integer to do calculations. Multiplying it by 1 will convert the varchar number to Integer

OUTPUT