꿈꾸는 개발자, DBA 커뮤니티 구루비

[퀴즈] IP 목록 정렬하기

by 마농 정규식 regexp_replace [2010.04.12 11:28:34]

[퀴즈] IP 목록 정렬하기

다음과 같이 IP 테이블을 정렬하는 쿼리를 작성하세요.

WITH t AS
(
SELECT '10.100.10.1' ip FROM dual
UNION ALL SELECT '10.100.1.10' FROM dual
UNION ALL SELECT '100.10.1.10' FROM dual
UNION ALL SELECT '100.10.1.20' FROM dual
UNION ALL SELECT '2.10.1.140' FROM dual
)
SELECT ROWNUM rn, ip FROM t;

[원본]		[결과]
RN	IP	RN	IP
1	10.100.10.1	5	002.010.001.140
2	10.100.1.10	2	010.100.001.010
3	100.10.1.10	1	010.100.010.001
4	100.10.1.20	3	100.010.001.010
5	2.10.1.140	4	100.010.001.020

[정답보기] <== 트리플클릭
SELECT ROWNUM rn
, REGEXP_REPLACE(REPLACE('.'||ip, '.', '.00'), '([^.]{3}(\.|$))|.', '\1') ip
FROM t
ORDER BY ip
;

by 서성우 [2010.04.12 14:41:01]

WITH t AS
(
SELECT '10.100.10.1' ip FROM dual
UNION ALL SELECT '10.100.1.10' FROM dual
UNION ALL SELECT '100.10.1.10' FROM dual
UNION ALL SELECT '100.10.1.20' FROM dual
UNION ALL SELECT '2.10.1.140' FROM dual
)
SELECT ROWNUM rn,
LPad(SubStr(ip , 1 , InStr(ip,'.',1,1)-1),3,'0')|| '.' ||
LPad(SubStr(ip , InStr(ip,'.',1,1)+1 , InStr(ip,'.',1,2)-InStr(ip,'.',1,1)- 1),3,'0') || '.'||
LPad(SubStr(ip , InStr(ip,'.',1,2)+1 , InStr(ip,'.',1,3)-InStr(ip,'.',1,2)- 1),3,'0') || '.'||
LPad(SubStr(ip , InStr(ip,'.',1,3)+1 ),3,'0') AS IP
FROM t
ORDER BY 2

오라클 9i라 태그에 있는 방법은 사용 하지 못하였고
위와 같이 노가다로....

by v상이v [2010.04.13 09:19:48]

WITH t AS
(
SELECT '10.100.10.1' ip FROM dual
UNION ALL SELECT '10.100.1.10' FROM dual
UNION ALL SELECT '100.10.1.10' FROM dual
UNION ALL SELECT '100.10.1.20' FROM dual
UNION ALL SELECT '2.10.1.140' FROM dual
)
SELECT ROWNUM rn
,LPAD(regexp_replace (ip,'(.*)\.(.*)\.(.*)\.(.*)', '\1'),3,0)||'.'||
LPAD(regexp_replace (ip,'(.*)\.(.*)\.(.*)\.(.*)', '\2'),3,0)||'.'||
LPAD(regexp_replace (ip,'(.*)\.(.*)\.(.*)\.(.*)', '\3'),3,0)||'.'||
LPAD(regexp_replace (ip,'(.*)\.(.*)\.(.*)\.(.*)', '\4'),3,0) ip
FROM t
ORDER BY ip

by 마농 [2010.04.14 09:09:24]

정답 올렸습니다.
그리고 이건 10g이하 버전용으로 만들어 본거입니다.

WITH t AS
(
SELECT '10.100.10.1' ip FROM dual
UNION ALL SELECT '10.100.1.10' FROM dual
UNION ALL SELECT '100.10.1.10' FROM dual
UNION ALL SELECT '100.10.1.20' FROM dual
UNION ALL SELECT '2.10.1.140' FROM dual
)
SELECT ROWNUM rn
, SUBSTR(REPLACE('.'||ip, '.', '.00'), INSTR(ip,'.',1,1), 4)
|| SUBSTR(REPLACE('.'||ip, '.', '.00'), INSTR(ip,'.',1,2)+2, 4)
|| SUBSTR(REPLACE('.'||ip, '.', '.00'), INSTR(ip,'.',1,3)+4, 4)
|| SUBSTR(REPLACE('.'||ip, '.', '.00'), -3) ip
FROM t
ORDER BY ip
;

by 선모 [2010.05.18 18:07:48]

by 선모 [2010.05.18 18:09:11]

마농님 죄송한데요..이쪽부분 이해가 안되서 그러는데..

풀어서 설명 좀 해주세요..

REGEXP_REPLACE(REPLACE('.'||ip, '.', '.00'), '([^.]{3}(\.|$))|.', '\1') ip

by 마농 [2010.05.19 15:08:42]

REGEXP_REPLACE(REPLACE('.'||ip, '.', '.00'), '([^.]{3}(\.|$))|.', '\1') ip
1. 우선 .을 하나 붙이고 : '.'||ip
(10.100.10.1 ==> .10.100.10.1)
2. .을 .00으로 변경 : REPLACE('.'||ip, '.', '.00')
(.10.100.10.1 ==> .0010.00100.0010.001)
3. 1번패턴(.이 아닌 문자열 3개 + (. or 끝)) or 2번패턴(기타모든 문자열) 을 첫번째 괄호에 해당하는 패턴으로 변경
바꿔 말하면 1번패턴은 놔두고 2번패턴은 삭제
REGEXP_REPLACE(REPLACE('.'||ip, '.', '.00'), '([^.]{3}(\.|$))|.', '\1')

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 퀴즈 및 튜닝 문제