데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 31307 게시물 읽기

No. 31307

전화번호 12자리로 만들어 추출하기
작성자	이정호(durinara)	작성일	2007-07-10 19:25	조회수	5,559

완전 초보입니다.

입력된 전화번호들을 12자리로 포맷을 맞춰서 추출해야 하는데, 쉽지 않네요.

예) 02-123-4567 => 000201234567 (입력된 값에는 하이픈이 들어가 있습니다.)

select lpad(replace(phone_number,'-',''),12,0) 와 같이 하면,

무조건 앞에서부터 0을 채워 자리수를 맞춰버려서 국번이 3자리인 경우 문제가 되니,

다른 방법이 있는지 알려주시면 감사하겠습니다.

ㅠ.ㅠ 프로그램 안쓰고 쿼리 하나로 해결하려니 잘 안되네요.

이 글에 대한 댓글이 총 8건 있습니다.

SELECT DECODE(SUBSTR(SVC_NO, 1, 2), '02',                                                <- a
                           LPAD(SUBSTR(SVC_NO, 1, 2), 4, '0') ||                                <- 1
                           LPAD(SUBSTR(SVC_NO, 3, LENGTH(SVC_NO) - 6), 4, '0') || <- 2
                           SUBSTR(SVC_NO, LENGTH(SVC_NO) - 3),                            <- 3

                           LPAD(SUBSTR(SVC_NO, 1, 3), 4, '0') ||                                 <- 4
                           LPAD(SUBSTR(SVC_NO, 4, LENGTH(SVC_NO) - 7), 4, '0') ||  <- 5
                           SUBSTR(SVC_NO, LENGTH(SVC_NO) - 3))                            <- 6
   FROM DUAL;

전화번호는   02-xxx-yyyy, 02-xxxx-yyyy 일때 각각 9Byte, 10Byte이고
                064-xxx-yyyy, 064-xxxx-yyyy 일때 각각 10Byte, 11Byte로 나누어 진다고 가정할때

a) 전화번호가 '02'로 시작한다고 가정할떄
    1) 전화번호 앞에서 2자리로 '0002' 편성
    2) 전화번호 3번째 숫자부터 전화번호의 Byte가 9 또는 10Byte가 될수 있음으로 지역번호 '02'의
        2Byte와 일련번호 4Byte(무조건 4Byte라고 간주해도 무방)를 더한 길이를 빼서 지역번호 xxx
        또는 xxxx를 4Byte의 국번으로 편성
    3) 전화번호 문자열의 뒤에서부터 총길이가 9일때 3을 빼면 6번째, 10일때 3을 빼면 7번째부터
        나머지 전화번호를 추출하여 일련번호 편성
?) 4), 5), 6)은 전화번호가 '02'가 아닌 기타지역일때 입니다...
    차이점은 지역번호를 잘라낼때의 1,2 가 1,3으로 바뀌었고
                 국번은 3번째부터 -6을 잘라냈던것을 4번째부터 -7로 잘랐으며
                 일련번호는 3)과 동일한 기준으로 자르시면 됩니다...

김권유(eros)님이 2007-07-10 21:09에 작성한 댓글입니다.

상세한 답변 정말 감사합니다.

이정호(durinara)님이 2007-07-11 09:16에 작성한 댓글입니다.

물론 DECODE로 CASE마다 코딩해주는 방법도 있겠지만, 질문에 하이픈이 들어간다고 하니 이를 이용하여 모든 CASE를 커버할 수 있는 쿼리를 만드는게 나을 듯 합니다.

또한 현재의 전화번호 체계에는 평생번호라고 하는 0505로 시작하는 번호도 있습니다.

아래 쿼리 결과 참고하세요...;

SELECT

TO_CHAR(SUBSTR(svc_no, 1, INSTR(svc_no,'-',1,1) - 1), 'FM0000') ||

TO_CHAR(SUBSTR(svc_no, INSTR(svc_no,'-',1,1) + 1, INSTR(svc_no,'-',1,2) - INSTR(svc_no,'-',1,1) - 1), 'FM0000') ||

SUBSTR(svc_no, INSTR(svc_no,'-',1,2) + 1) AS svc_no

FROM(

SELECT '02-123-4567' AS svc_no FROM dual UNION ALL

SELECT '032-456-7890' AS svc_no FROM dual UNION ALL

SELECT '010-1234-5678' AS svc_no FROM dual UNION ALL

SELECT '0505-2345-6789' AS svc_no FROM dual

)

건승하시길...수고하세요~~

성시현(finecomp)님이 2007-07-11 10:01에 작성한 댓글입니다.
이 댓글은 2007-07-11 10:06에 마지막으로 수정되었습니다.

저도 substr 과 instr 을 사용하면;;; 더 심플하다에 1표~

다크템플러님이 2007-07-11 11:37에 작성한 댓글입니다. Edit Ｘ

select lpad(substr(tel,1,instr(tel,'-',1,1)-1),4,'0')||
       lpad(substr(tel,instr(tel,'-',1,1)+1,instr(tel,'-',1,2)-instr(tel,'-',1,1)-1),4,'0')||
       lpad(substr(tel,instr(tel,'-',1,2)+1,length(tel)-instr(tel,'-',1,2)),4,'0')
from (
    select '02-123-4567' as tel from dual union all
    select '031-123-4567'as tel from dual union all
    select '031-1234-567'as tel from dual union all
    select '031-12-567' as tel from dual
    );

이것저것 하다 보니 길어졌네요

정용석(libranet)님이 2007-07-11 12:12에 작성한 댓글입니다.

다템이다~ 워~ ㄷㄷ

드라군님이 2007-07-11 12:27에 작성한 댓글입니다. Edit Ｘ

아.. 이런 방법도 있군요.
답변주신 분들 모두 감사합니다. ^^/

이정호(durinara)님이 2007-07-11 13:02에 작성한 댓글입니다.

마지막 번호가 4자리인 것이 보장된다면 조금 심플하게...

LPAD( SUBSTR(tel, 1, INSTR(tel, '-')-1), 4, '0') ||
LPAD(REPLACE(SUBSTR(tel, INSTR(tel, '-')+1), '-', ''),8, '0')

전영식(garam111)님이 2009-04-10 07:14에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
31310	create trigger시 에러가 나는데.. ㅠ,ㅜ [2]	타쿠미	2007-07-11	6912
31309	날짜함수 쿼리 질문입니다. [1]	웁스	2007-07-11	2950
31308	connect by prior 질문드립니다. [2]	김도형	2007-07-11	2002
31307	전화번호 12자리로 만들어 추출하기 [8]	이정호	2007-07-10	5559
31306	oracle 관련 책점 추천 해주세요~ [1]	이준성	2007-07-10	923
31305	decode에 대하여 질문이 있습니다. [5]	무심도	2007-07-10	1412
31304	쿼리가 궁금해서요. [1]	김지연	2007-07-10	1255