Vertigo448 February 2016

SQL Server : selecting Ips form Ips range

I'm quite new to SQL and I kind off hit the wall with this:

I have quite simple SQL query but I need to modify that that query to select only IP_ADDRESS from IPs range 100.64.0.0 – 100.127.255.255 and 10.0.0.0 – 10.255.255.255. I know that I need to use regexp for that but do not know how? Can You help me?

Original query:

SELECT 
    sal.IP_ADDRESS, sal.COUNTRY_CODE, sal.EVENT_ID, p.name, p.PARTNER_ID
FROM 
    EVENTS_USER.STREAM_ACCESS_LOGS sal, EVENTS_USER.STREAM_USERS su, EVENT_USER.PARTNERS p
WHERE 
    su.PARTNER_ID = p.PARTNER_ID
    AND sal.SUCESS = 'Y'
    AND sal.COUNTRY_CODE is null;

Could this regexp look like that?:

SELECT * FROM EVENTS_USER.STREAM_ACCESS_LOGS(
select regexp_replace(regexp_replace('100.64.0.0', '(\d+)', '00\1'), '0*(\d{3})', '\1')  IP_ADDRESS from dual 
UNION ALL 
select regexp_replace(regexp_replace('100.127.255.255', '(\d+)', '00\1'), '0*(\d{3})', '\1') from dual
) ORDER BY IP_ADDRESS; 

Answers


miken32 February 2016

In MySQL we have the INET_ATON() function, which converts an address into a 32-bit integer. This answer to another question (not sure if this qualifies as a duplicate?) provides some equivalent SQL Server code:

create function INET_ATON (@addr varchar(15))
returns bigint
with schemabinding
as
begin
  return
    cast(parsename(@addr, 4) as bigint) * 16777216 +
    cast(parsename(@addr, 3) as bigint) * 65536 +
    cast(parsename(@addr, 2) as bigint) * 256 +
    cast(parsename(@addr, 1) as bigint)
end

You could then add this into your query:

SELECT 
    sal.IP_ADDRESS, sal.COUNTRY_CODE, sal.EVENT_ID, p.name, p.PARTNER_ID
FROM 
    EVENTS_USER.STREAM_ACCESS_LOGS sal, EVENTS_USER.STREAM_USERS su, EVENT_USER.PARTNERS p
WHERE 
    su.PARTNER_ID = p.PARTNER_ID
    AND sal.SUCESS = 'Y'
    AND sal.COUNTRY_CODE is null
    AND (
        INET_ATON(sal.IP_ADDRESS) BETWEEN INET_ATON("100.64.0.0") AND INET_ATON("100.127.255.255") OR
        INET_ATON(sal.IP_ADDRESS) BETWEEN INET_ATON("10.0.0.0") AND INET_ATON("10.255.255.255")
    )

Post Status

Asked in February 2016
Viewed 3,811 times
Voted 5
Answered 1 times

Search




Leave an answer