데이터베이스 질문과 답변
안녕하세요.
문장속에 포함된 IP주소를 추출하는 쿼리를 작성하고 있습니다.
아래와 같이 작성하였는데, 유효하지 않은 IP주소(홍길동의 IP주소)가 추출됩니다.
유효한 IP만 추출할 수 있는 정규식 조언 부탁드립니다. 오라클 11g 사용하고 있습니다.
유효한 IP : 0.0.0.0 ~ 255.255.255.255
WITH TMP AS
(SELECT '홍길동의 IP주소는 323.211.123.11 입니다.' IP FROM DUAL UNION ALL
SELECT 'IP : 10.10.10.1 입니다.' FROM DUAL UNION ALL
SELECT '1.2.3.4 도 추출되어야 하는 IP입니다.' FROM DUAL UNION ALL
SELECT '범위를 벗어난 IP 100.256.1.1 은 제외되어야 합니다.' FROM DUAL)
SELECT *
FROM TMP
WHERE REGEXP_LIKE
(IP
, '(([0-9]{1}|1[0-9]{1}|1[0-9]{2}|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]{1}|1[0-9]{1}|1[0-9]{2}|1[0-9]{2}|2[0-4][0-9]|25[0-5]){1}');
by 마농
[2016.06.03 15:25:12]
323.211.123.11 이 포함되는 이유는?
앞자리를 제외시키면 조건을 만족하기 때문이죠.
23.211.123.11
3.211.123.11
(^|[^0-9]) 요거 맨 앞에 붙여주면 될 듯.
by 마농
[2016.06.03 15:28:23]
음.. 다시 생각해보니 맨 뒷자리도 마찬가지 오류가 나올 듯.
예) 10.10.10.444 도 마찬가지 이유로 출력 될 듯.
(^|[^0-9]) 요거 맨 앞에 붙여주고 ($|[^0-9]) 요거 맨 뒤에 붙여주기.
by 신이만든짝퉁
[2016.06.03 15:57:05]
WITH TMP AS
(SELECT '홍길동의 IP주소는 323.211.123.11 입니다' IP FROM DUAL UNION ALL
SELECT 'IP : 10.10.10.1 입니다.' FROM DUAL UNION ALL
SELECT 'IP : 310.10.10.1 입니다.' FROM DUAL UNION ALL
SELECT 'IP : 10.10.10.432 입니다.' FROM DUAL UNION ALL
SELECT '1.2.3.123 도 추출되어야 하는 IP입니다.' FROM DUAL UNION ALL
SELECT '범위를 벗어난 IP 100.256.1.1 은 제외되어야 합니다.' FROM DUAL)
SELECT REGEXP_REPLACE(REGEXP_REPLACE(IP, '[^0-9\.]', ''), '([^\.]+)(\.*)$', '\1') IP
FROM TMP
WHERE REGEXP_LIKE
(IP
, '(^|[^0-9])(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])($|[^0-9])');
말씀듣고, 최종적으로 이렇게 했습니다. 감사합니다. ^___^
더 좋은 의견 있으시면 언제나 경청하겠습니다. ^_^
by 신이만든짝퉁
[2016.06.03 18:37:09]
WITH TMP AS
(SELECT '홍길동의 IP주소는 323.211.123.11 입니다' IP FROM DUAL UNION ALL
SELECT '앞에 123 같은 숫자가 포함되어 있을 때 IP : 10.10.10.1 입니다..' FROM DUAL UNION ALL
SELECT '유효하지 않은 IP : 310.10.10.1 입니다..' FROM DUAL UNION ALL
SELECT '이것도 유효하지 IP : 10.10.10.432 입니다.' FROM DUAL UNION ALL
SELECT '1.2.3.123 도 추출되어야 하는 IP입니다.' FROM DUAL UNION ALL
SELECT '범위를 벗어난 IP 100.256.1.1 은 제외되어야 합니다.' FROM DUAL)
SELECT TRIM
(REGEXP_SUBSTR
(IP
, '(^|[^0-9])(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])($|[^0-9])'
, 1)) IP
FROM TMP
WHERE REGEXP_LIKE
(IP
, '(^|[^0-9])(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])($|[^0-9])');
요렇게 하는 게 더 정확하게 나오네요.
by 마농
[2016.07.05 11:12:29]
SELECT ip
FROM tmp
, (SELECT '([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])' x FROM dual) -- (0 ~ 255)
WHERE REGEXP_LIKE(ip, '(^|[^0-9])'||x||'(\.'||x||'){3}($|[^0-9])')
;
) 버튼을 클릭하여 작성 하시면 됩니다.