var service_url = 'test01.test02.com/test03/test04/test05'
select :service_url
,protocol2
,substr(protocol2,2,instr(protocol2,'/',1,2)-2) as t01
,substr(protocol2,2,instr(protocol2,'/',1,3)-2) as t02
,instr(protocol2,'/',1,2)
from
(
select :service_url
,regexp_replace(:service_url,'(http[s]?://)?(.*?)((/|:)(.)*|$)', '\1') protocol1
,regexp_replace(:service_url,'(http[s]?://)?(.*?)((/|:)(.)*|$)', '\2') hostname
,regexp_replace(:service_url,'(http[s]?://)?(.*?)((/|:)(.)*|$)', '\3') protocol2
from dual
)
;
원하는 결과가
test01 test02 test03 test04 test05
이런식으로 각 컬럼별로 받고 싶은데, 위에 한것 처럼 이것저것 해봐도 잘 안되네요
혹시 답변 부탁드려도 될지 모르겠습니다.
com 은 왜 빠지죠?
http://gurubee.net/article/83527 에서는 결과가 어찌 나와야 할까요?
http://www.gurubee.net/article/83527 에서는 결과가 어찌 나와야 할까요?
com 이 빠진다면? net 이나 co.kr 이나 go.kr 등. 기타 다양한 예외사항이 존재할 듯 한데요?
WITH t AS ( SELECT 'test01.test02.com/test03/test04/test05' service_url FROM dual UNION ALL SELECT 'http://gurubee.net/article/83527' FROM dual UNION ALL SELECT 'https://www.gurubee.net/article/83527' FROM dual UNION ALL SELECT 'gurubee.net/article/83527' FROM dual UNION ALL SELECT 'gurubee.net:8888/article/83527' FROM dual UNION ALL SELECT 'http://markets.points.com/main/point/report' FROM dual UNION ALL SELECT 'https://markets.cloud.com/main/report/service' FROM dual ) SELECT service_url , v1, v2, v3 , REGEXP_SUBSTR(v2, '[^.]+', 1, 1) x1 , REGEXP_SUBSTR(v2, '[^.]+', 1, 2) x2 , REGEXP_SUBSTR(v3, '[^/]+', 1, 1) x3 , REGEXP_SUBSTR(v3, '[^/]+', 1, 2) x4 , REGEXP_SUBSTR(v3, '[^/]+', 1, 3) x5 FROM (SELECT service_url , REGEXP_REPLACE(service_url, '([^:]+://)?([^/]+)(.*)', '\1') v1 , REGEXP_REPLACE(service_url, '([^:]+://)?([^/]+)(.*)', '\2') v2 , REGEXP_REPLACE(service_url, '([^:]+://)?([^/]+)(.*)', '\3') v3 FROM t ) ;