데이터베이스 질문과 답변
select
REGEXP_REPLACE(ap_number, '(\d{3})(\d+)(\d{4})', '\1-\2-\3')
FROM
(
select '01034502866' as ap_number from dual
union all
select '01034508194' as ap_number from dual
union all
select '01034512377' as ap_number from dual
union all
select '01034512877' as ap_number from dual
union all
select '01034516190' as ap_number from dual
)
현재 이 쿼리를 실행중인데요
00011112222 형식의 전화번호를
000-1111-2222 형식으로 만듬니다.
그런데 이걸
000-****-2222 형식으로 표현하고 싶은데 어떻게 수정해야할까요? 감사합니다.
현재 이 쿼리를 실행중인데요
00011112222 형식의 전화번호를
000-1111-2222 형식으로 만듬니다.
그런데 이걸
000-****-2222 형식으로 표현하고 싶은데 어떻게 수정해야할까요? 감사합니다.
by 드리미스
[2012.07.27 13:05:02]
-- 음 제가 아직 잘은 몰라서.. 앞에 답변 달아주신걸 응용해서 답변드립니다
with t as (
select '01034502866' as ap_number from dual
union all select '01034508194' as ap_number from dual
union all select '01034512377' as ap_number from dual
union all select '01034512877' as ap_number from dual
union all select '01034516190' as ap_number from dual
)
SELECT ap_number
, REGEXP_REPLACE(ap_number
, '^(02|031|032|033|041|042|043|051|052|053|054|055|061|062|063|064|010|011|016|017|018|019|070|080)(.*)(....)$'
, '\1-****-\3'
) v
FROM t
;
by 손님
[2012.07.27 13:41:32]
네 ****은 저도 생각해보았는데.. 가운데 자릿수가 3자리일수도 4자리일수도 있기에 ... 가변적인걸 어떻게 알수있나해서요
by 제로
[2012.07.27 14:03:22]
with t as
( select '0103452866' as ap_number from dual union all
select '01034508194' as ap_number from dual union all
select '01034512377' as ap_number from dual union all
select '01034512877' as ap_number from dual union all
select '01034516190' as ap_number from dual
)
select REGEXP_REPLACE(c0, '-(.*)-', '-'||LPAD('*', c1, '*')||'-')
from (select REGEXP_REPLACE(ap_number, '(.{3})(.*)(.{4})', '\1-\2-\3') c0
, LENGTH(REGEXP_REPLACE(ap_number, '(.{3})(.*)(.{4})', '\2')) c1
from t);
) 버튼을 클릭하여 작성 하시면 됩니다.