ms-sql에서 쿼리를 이용하여 숫자를 제외한 모든 문자를 제외하는 방법

카페 > 어리버리 범준이의 까페 | 어리버리
http://cafe.naver.com/keidao/27

 

ms-sql에서 쿼리를 이용하여 숫자를 제외한 모든 문자를 제외하는 방법??

답변이 완료된 질문입니다. (2004-01-08 05:15 작성)

ms-sql에서 쿼리를 이용하여 숫자를 제외한 모든 문자를 제외하는 방법이 있을까요?? 예를 들면.. 011******* 011******** 011***2356 011*322*5441 011*9021*8301부천시소사구 011*9993*3988 011/8499967 011???????? 011_702_1406강서구등촌동 011`8198489 011`91495705 011~9864~4380 011+145=바보 이런 값에서 숫자 사이와 뒤에 있는 모든 문자열등을 제회하고 숫자만나오게 할수 있을까요?? 결과적으로 핸드폰 번호만 나오게 하는 방법을 질문합니다. 또한 각 레코드의 결과값의 핸드폰번호들은 공백없이 다 붙게 하는 방법좀 알려주세요 그리고 한가지가 더 있어요.. 핸드폰 번호가 뒤죽박죽이자나요... 최소 10자리에서 최대 11자리까지... 그럼...10자리이하나 11자리 이상인 값은 제외하고 10자리와 11자리값만 동시에 출력하는 방법도 덤으로 알려주세요 참고로 샘플로 쓰인 번호는 제가 직접 임의로 작성한겁니다... 고수님들의 멋진 답변 부탁합니다....

답변

가령 예를 들어서 016-232-2421 이라는 핸드폰 번호가 있구요, 이중에서 "-"만 제외하고 0162322421 이라는 번호로 만든다고 가정을 하지요. 우선 선수지식으로 문자열 관련 함수를 알아야 합니다. 1. ASCII(expr) expr의 맨 왼쪽값(즉, 최초의 1글자)을 아스키 코드값으로 반환합니다. 이것은 숫자인지 문자인지 알아내기 위함이구요, 0-9까지의 숫자는 아스키 코드로 48-57까지의 값을 갖습니다(참조 : http://penlee.inje.ac.kr/lecture/cprgm/y96spring/asciitbl.htm). 가령 ASCII('0') 하게 되면 48이란 값을 반환하죠 2. SUBSTRING(expr, start, length) expr의 start 위치로부터 length의 길이만큼 문자열을 추출 합니다. 1번에서의 아스키 코드값으로 변환하기 위한 값을 추출하기 위해 사용하지요. start 위치는 맨 왼쪽의 글자가 1부터 시작합니다. 3. If 문의 사용 If 조건식 Begin 조건식이 참일 때 처리할 구문들 End Else Begin 조건식이 거짓일 때 처리할 구문들 End 4. While 문의 사용 While 조건식 Begin 조건식이 참인 동안 처리할 구문들 End 5. Len(char_expr) Len 함수는 문자열 값을 받아서 해당 문자열의 길이를 반환합니다. 6. 변수의 선언 및 값의 할당 변수의 선언은 Declare 문을 통해서, 값의 할당은 Set 문을 통해서 합니다. 변수 이름은 "@"로 시작하죠 Declare @변수명 변수의_형식 Set @변수명 = 할당할_ 값 그럼 이것을 갖고 한 번 만들어 보지요. 1. 우선 추출할 값을 변수에 넣어두고 해보도록 하지요. 값은 12자리 또는 13자리이므로 varchar 형식으로 선언합니다. 여유를 두어 15자리로 선언했습니다. 크기는 값에 따라서 적절히 변동시키시기 바랍니다. 마지막으로 중간에 숫자가 아닌 값을 제거시킨 최종값을 저장할 변수도 선언합니다. Declare @h_num varchar(15) Set @h_num = '016-232-2421' Declare @h_num_last varchar(15) -- 모든 연산이 끝난 후의 값을 저장할 변수 Set @h_num_last = '' -- 우선 초기화를 시킵니다 2. SQL에는 For ~ Next와 같은 반복 회수가 지정된 반복문이 없으므로 반복 회수를 정하기 위해 우선 길이 값을 변수에 저장해 둡니다. 12라는 값이 @h_num_len 변수에 저장될 것이구요, 값의 크기가 작으므로 smallint 형식으로 선언했습니다. Declare @h_num_len smallint Set @h_num_len = Len(@h_num) 3. While 문을 통해 문자열의 길이만큼 반복하면서 각 자리의 값을 喪銖爛求? 반복 회수 비교를 위한 카운터 변수가 필요하니 카운터 변수 정의 후 반복하죠. 첫번째 글자부터 반복하니깐 초기값은 당연히 1입니다. 반복 처리는 문자열의 길이만큼 하므로 조건식은 아래와 같이 줍니다. Declare @count smallint Set @count = 1 While @count <= @h_num_len Begin 4부터 시작할 실제 처리 구문 End 그럼 While 문 안에 들어가는 처리 구문을 작성해보죠. 4. 문자열의 첫번째 위치로부터 1글자씩 추출하여 Ascii 코드값 비교 문자열 추출함수를 이용하여 추출합니다. 우선 문자열 추출은 다음과 같이 되겠죠. SubString(@h_num, start, 1) 한 글자씩 추출하니깐 세번째 인자인 추출길이는 당연히 1이구요, 값은 이미 저장되어 있으니 첫번째 인자에 값을 저장해 놓은 변수명을 써 주었습니다. 문제는 추출 위치를 정하는 것인데, start 값은 1, 2, 3,...번째 글자 이렇게 가니깐 While 구문 이전에 정의해 놓은 @count 변수를 이용하면 되고 이 변수를 맨 나중에 1씩 증가시켜 주면 될 겁니다. 또 1개씩 값이 추출되면서 이 임시값을 저장할 변수가 필요합니다. 따라서 우선 변수를 정의하고 값을 저장해 줍니다. 길이가 1이므로 char(1) 형식으로 선언하죠 Declare @temp_str char(1) Set @temp_str = SubString(@h_num, @count, 1) 5. @temp_str 변수에 숫자인지의 여부를 검사하도록 If 문을 만듭니다. 조건은 추출한 문자의 ASCII 코드값의 범위를 비교해서 참일 경우 기존에 추출한 값과 연결해서 저장하도록 합니다 If (ASCII(@temp_str) >= 48 and ASCII(@temp_str) <= 57) Begin Set @h_num_last = @h_num_last & @temp_str End If 문에서의 Begin ~ End를 통해 묶어주는 이유는 If 문이 끝난다는 표시가 없기 때문에 원래는 다음의 형식을 같습니다. If 조건식 조건식이 참일 때 실행할 구문 1개 Else 조건식이 거짓일 때 실행할 구문 1개 그렇기 때문에 여러 개의 구문을 실행할 경우 하나의 블록으로 묶어 주는 Begin ~ End 문을 사용하는 것입니다. 그건 맨 위의 선수지식 부문에 요약되어 있습니다. 지금 현재는 1개의 구문만 실행하므로 굳이 Begin ~ End 로 묶을 필요는 없습니다. 또 꼭 ASCII 함수를 써서 비교하지 않더라도 IsNumeric 함수를 쓰는 방법도 있습니다. IsNumeric 함수는 값을 받아서 해당 값이 숫자인지 여부를 살펴 숫자이면 True 값을 반환합니다 따라서 위 If 문은 간단히 다음과 같이 변경이 가능합니다. If IsNumeric(@temp_str) Begin Set @h_num_last = @h_num_last & @temp_str End 조건식을 만족할 경우(즉, 추출한 1개의 문자가 숫자일 경우)에만 기존의 값에다 연결하여 저장하므로 Else 구문은 필요없게 됩니다. 6. 이제 글자를 처리했으므로 다음 글자로 start 위치를 변경시켜야 하므로 카운터 변수를 1 증가시킵니다. Set @count = @count + 1 7. 그럼 While 구문을 다시 완성해보도록 하지요. Declare @count smallint Set @count = 1 While @count <= @h_num_len Begin Declare @temp_str char(1) Set @temp_str = SubString(@h_num, @count, 1) If ((ASCII(@temp_str) >= 48) and (ASCII(@temp_str) <= 57)) Begin Set @h_num_last = @h_num_last & @temp_str End Set @count = @count + 1 End 조금 복잡하지요?? Text 에디터에 복사하신 후 적절히 띄어쓰기를 통해서 보기 편하게 바꿔서 보시기 바랍니다. 8. 여기까지 하게 되면...이제는 숫자만 추출이 끝났으므로 추출한 값을 저장한 변수로부터 값을 출력시키면 됩니다. Select @h_num_last 휴우~ 끝났군요. 직접 테스트한 전체 구문은 다음과 같습니다. Declare @h_num varchar(15) Set @h_num = '016-232-2421' Declare @h_num_last varchar(15) -- 모든 연산이 끝난 후의 값을 저장할 변수 Set @h_num_last = '' -- 우선 초기화를 시킵니다 Declare @h_num_len smallint Set @h_num_len = Len(@h_num) Declare @count smallint Set @count = 1 While @count <= @h_num_len Begin Declare @temp_str char(1) Set @temp_str = SubString(@h_num, @count, 1) If ((ASCII(@temp_str) >= 48) and (ASCII(@temp_str) <= 57)) Begin Set @h_num_last = @h_num_last + @temp_str End Set @count = @count + 1 End Select @h_num_last 결과 0162322421 값이 정상적으로 출력됩니다. 다른 값으로도 해보았구요. 이제 이것을 이용해서 Stored Procedure나 사용자 정의 함수로 선언해서 사용하시면 되겠지요?? 그 부분은 숙제로 남겨두도록 하지요. PS) 덤으로 물어보신 것은...지금 여기서 설명한 함수 이용하시면 되겠죠? 값을 Len 함수로 길이값을 반환시킨 다음에 If 문을 통해서 걸러내고 위에 작성한 구문들을 사용하면 되지요. 충분히 아실 수 있으리라 봅니다. PS2) 여기에 쓰인 기본 지식들은 일반적인 프로그래밍 공부를 좀 하신 분들이라면 기본적으로 널리 쓰이는 개념들입니다. 열심히 공부하세요.