SQL Server : selecting Ips form Ips range

Question

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;

SQL Server doesn't support Regex, unless it's a new feature in 2016 that I don't know about yet. Look into using PARSENAME(). — Tab Alleman, Feb 08 '16 at 21:55
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Feb 08 '16 at 21:56
Hi, thanks for quick respons. This my first larger SQL query than just simple SELECT * FROM ;) thanks for advise about bad habits, I will try to learn from that and do not make same mistake twice thanks — Vertigo448, Feb 08 '16 at 22:06

score 0 · Answer 1 · edited May 23 '17 at 12:31

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")
    )

SQL Server : selecting Ips form Ips range

1 Answers1