A테이블
B테이블
조인 할 예정
A테이블엔 서브넷으로 IP가 들어가있음.
B테이블엔 서브넷이 아닌 IP들이 많이 들어가있음
B테이블의 IP를 판별해서 해당 서브넷에 포함되면 A테이블의 서브넷 IP를 조인할예정
EX)
A테이블
-----------------------------------
subnet
192.168.0.0/24
192.168.1.0/24
-----------------------------------
B테이블
-----------------------------------
IP
192.168.0.3
192.168.0.5
192.168.1.4
192.168.1.10
-----------------------------------
아우터 조인
-----------------------------------
IP | subnet
192.168.0.3 | 192.168.0.0/24
192.168.0.5 | 192.168.0.0/24
192.168.1.4 | 192.168.1.0/24
192.168.1.10 | 192.168.1.0/24
-----------------------------------
가능할까요
이런 식으로 될까요?
with tab_a (subnet) as ( select '192.168.0.0/24' union all select '192.168.1.0/24' ), tab_b (ip) as ( select '192.168.0.3' union all select '192.168.0.5' union all select '192.168.1.4' union all select '192.168.1.10' ) select tab_b.ip, tab_a.subnet from tab_a left outer join tab_b on tab_b.ip like left(tab_a.subnet,9) + '%'
생각이 짧았네요 ^^; 좀 바꿔봤습니다. parsename을 응용하시면 어떨까합니다.
with tab_a (subnet) as ( select '192.168.0.0/24' union all select '192.168.1.0/24' union all select '192.168.10.0/24' ), tab_b (ip) as ( select '192.168.0.3' union all select '192.168.0.5' union all select '192.168.1.4' union all select '192.168.1.10' ) select tab_b.ip, tab_a.subnet from tab_a left outer join tab_b on parsename(tab_b.ip,2) = parsename(tab_a.subnet,2) and parsename(tab_b.ip,3) = parsename(tab_a.subnet,3) and parsename(tab_b.ip,4) = parsename(tab_a.subnet,4)
WITH tab_a AS ( SELECT '192.168.0.0/24' subnet UNION ALL SELECT '192.168.1.0/24' UNION ALL SELECT '192.168.10.0/24' ) , tab_b AS ( SELECT '192.168.0.3' ip UNION ALL SELECT '192.168.0.5' UNION ALL SELECT '192.168.1.4' UNION ALL SELECT '192.168.1.10' ) SELECT * FROM tab_b INNER JOIN tab_a ON CHARINDEX(LEFT(ip, CHARINDEX('.', ip, CHARINDEX('.', ip, CHARINDEX('.', ip) + 1) + 1)), subnet) = 1 ;