영삼넷IT 강국 = 김영삼 블로그에 오신걸 환영합니다.

CID를 구축하는데 CID장비에서는 전화번호를 "-" 없이 번호를 붙여서 전송

고객 DB에는 고객 연락처에 "-"붙여서 사용.....

WHERE 절에 Replace(tel_no, '-', '') = @연락처....이렇게 하려니 조건절에 함수를 사용해서

꺼림직하여 프로시저로 작성



/*
	TITLE :  CID에서 오는 전화번호 중간에 "-" 정확하게 삽입

	작성자 : 지현명 
	작성일자 : 2009년 7월 20일
	내용 : 
	- 고객 DB에 전화번호와 핸드폰번호가 중간에 "-"가 삽입되어 저장
	- 기존 다른 App와 연동되어 있기 때문에 DB에 저장된 데이터를 "-"없이 변경 못함.
	- 컬럼 두개 추가하는것도 생각해 봤는데 용량만 차지....
	- CDI에서 "-"없이 들어오는 번호를 핸드폰/국선 구분하여 "-" 삽입후 고객연락처와 비교
	- WHERE절에서 Replace(TEL_NO_COM, '-', '') = @TEL_NO ..이렇게 하면 인덱스를  이용 못하고
 	- 검색시 상당히 오래 걸림 <- 정원혁 선생님 교제에 있음...

-- 국내 지역번호
--서울 02 경기 031 인천 032 강원 033 충남 041 대전 042 충북 043 부산 051 울산 052 대구 053 경북 054 경남 055 전남 061 광주 062 전북 063 제주 064 
--서울 말고는 전부 3자리...
*/

-- EXEC CID_CUST_INFO_SEARCH '01190773532'

ALTER PROC CID_CUST_INFO_SEARCH
	@TEL_NO VARCHAR(20)
AS


DECLARE @TEL_NO_1 		VARCHAR(3) --통신사/지역번호
DECLARE @TEL_NO_2 		VARCHAR(4) --중간번호
DECLARE @TEL_NO_3 		VARCHAR(4) -- 최종번호
DECLARE @TEL_NO_SEARCH VARCHAR(20) -- '-' 붙여진 최종번호
DECLARE @TEL_NO_TMP VARCHAR(20) --오리지널번호 임시 저장

SET @TEL_NO = LTRIM(RTRIM(@TEL_NO))
SET @TEL_NO_TMP  = @TEL_NO 

IF CHARINDEX('01',  LEFT(RTRIM(LTRIM(@TEL_NO)), 2)) = 1

BEGIN  -- 핸드폰 일 경우

	SET 	@TEL_NO_1 	=  LEFT(@TEL_NO, 3) -- 01190771234->011
--	SET  	@TEL_NO		=  REPLACE(@TEL_NO, @TEL_NO_1, '') -- 01190771234->90771234 
	SET  	@TEL_NO		=  SUBSTRING(@TEL_NO, 4, LEN(@TEL_NO)) --  통신사 뒷자리부터 잘라내기
	SET		@TEL_NO_3 	=  RIGHT(@TEL_NO, 4) --  90771234->1234
	SET 	@TEL_NO_2 	= 	REVERSE(SUBSTRING(REVERSE (@TEL_NO),  5, LEN(@TEL_NO)-4 )) -- 90771234->리버스->43217709->7709만 잘라냄..'-4'는 최종번호 잘라낸 자리수->리버스->9077

	SET 	@TEL_NO_SEARCH =  @TEL_NO_1 + '-' +  @TEL_NO_2 + '-' + @TEL_NO_3	
END
ELSE
BEGIN 
--일반전화
	IF LEFT(@TEL_NO, 2) = '02' 
	BEGIN
		SET 	@TEL_NO_1 	=  LEFT(@TEL_NO, 2) -- 0221041234 -> 02
	--	SET  	@TEL_NO		=  REPLACE(@TEL_NO, @TEL_NO_1, '') -- 0221041234->21041234
		SET  	@TEL_NO		=  SUBSTRING(@TEL_NO, 3, LEN(@TEL_NO))
		SET		@TEL_NO_3 	=  RIGHT(@TEL_NO, 4) --  21041234->1234
		SET 	@TEL_NO_2 	= 	REVERSE(SUBSTRING(REVERSE (@TEL_NO),  5, LEN(@TEL_NO)-4 )) -- 21041234->리버스->43214012->4012만 잘라냄..'-4'는 최종번호 잘라낸 자리수->리버스->2104
		
		SET 	@TEL_NO_SEARCH =  @TEL_NO_1 + '-' +  @TEL_NO_2 + '-' + @TEL_NO_3	

	END
	ELSE
	BEGIN
		SET 	@TEL_NO_1 	=  LEFT(@TEL_NO, 3) -- 0332542011 -> 031
	--	SET  	@TEL_NO		=  REPLACE(@TEL_NO, @TEL_NO_1, '') -- 0332542011->2542011
		SET  	@TEL_NO		=  SUBSTRING(@TEL_NO, 4, LEN(@TEL_NO))
		SET		@TEL_NO_3 	=  RIGHT(@TEL_NO, 4) --  21040152->0152
		SET 	@TEL_NO_2 	= 	REVERSE(SUBSTRING(REVERSE (@TEL_NO),  5, LEN(@TEL_NO)-4 )) -- 2542011->리버스->1102452->452만 잘라냄..'-4'는 최종번호 잘라낸 자리수->리버스->254
		
		SET 	@TEL_NO_SEARCH =  @TEL_NO_1 + '-' +  @TEL_NO_2 + '-' + @TEL_NO_3	
	END
END

SELECT 
	A.CUST_CD,
	A.CUST_NM,
	A.TEL_NO_COM,
	A.TEL_NO_MOB,
	A.STR_CD,
	(SELECT STR_CD_NM FROM RMSMST1 WITH(NOLOCK) WHERE STR_CD = A.STR_CD) STR_NM,
	A.CUST_RANK_DIV,
	 (SELECT DESCRP FROM RMBMCM1 WITH(NOLOCK)  WHERE GRP_CD = '58' AND DETAIL_CD = A.CUST_RANK_DIV) CUST_RANK, -- 등급명
	A.ZIPCODE,
	A.ADDR1 + ' ' + A.ADDR2  + ' '+ A.ADDR3 + ' ' + A.ADDR4  + '  '  + A.ADDR5 AS ADDR
FROM RCCMCS1 A WITH(NOLOCK)
WHERE 
	CASE 
		WHEN CHARINDEX('01',  LEFT(RTRIM(LTRIM(@TEL_NO_TMP)), 2)) = 1 THEN A.TEL_NO_MOB  --핸드폰일경우
		ELSE A.TEL_NO_COM --국선일경우
	END =  @TEL_NO_SEARCH