|
declare poi1_test cursor
for select endindex, fie001, fie002, fie003, fie004, fie005, fie006, fie007 from poi1
open poi1_test
DECLARE @STR VARCHAR(1000)
DECLARE @tmpSTR VARCHAR(100)
DECLARE @STRATINDEX INT
DECLARE @ENDINDEX INT
DECLARE @fie001 VARCHAR(100)
DECLARE @fie002 VARCHAR(100)
DECLARE @fie003 VARCHAR(100)
DECLARE @fie004 VARCHAR(100)
DECLARE @fie005 VARCHAR(100)
DECLARE @fie006 VARCHAR(100)
DECLARE @fie007 VARCHAR(100)
SET @STR= '서울 강남구 논현동 82-15'
SET @STRATINDEX = 1
SET @ENDINDEX = 1
WHILE(@ENDINDEX < LEN(@STR))
BEGIN
SET @ENDINDEX = CHARINDEX(' ', @STR, @STRATINDEX)
IF(@ENDINDEX = 0)
SET @ENDINDEX = LEN(@STR)
SET @tmpSTR = SUBSTRING(@STR, @STRATINDEX, @ENDINDEX - @STRATINDEX)
IF(RIGHT(@tmpSTR, 2)='서울')
SET @fie001 = @tmpSTR
else IF(RIGHT(@tmpSTR, 2)='대전')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='부산')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='광주')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='울산')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='제주')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='대구')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='인천')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='경기')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='경남')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='경북')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='충남')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='충북')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='전남')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='전북')
SET @fie001 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 2)='강원')
SET @fie001 = @tmpSTR
ELSE IF((RIGHT(@fie001, 2)='서울' OR RIGHT(@fie001, 2)='전북' OR RIGHT(@fie001, 2)='강원' OR
RIGHT(@fie001, 2)='전남' OR RIGHT(@fie001, 2)='경북' OR RIGHT(@fie001, 2)='경남' OR RIGHT(@fie001, 2)='충
북' OR RIGHT(@fie001, 2)='충남' OR RIGHT(@fie001, 2)='제주' OR RIGHT(@fie001, 2)='경기' OR RIGHT(@fie001, 2)
='인천' OR RIGHT(@fie001, 2)='광주' OR RIGHT(@fie001, 2)='울산' OR RIGHT(@fie001, 2)='부산' OR RIGHT
(@fie001, 2)='대구' OR RIGHT(@fie001, 2)='대전') AND RIGHT(@tmpSTR, 1)='구')
SET @fie002 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 1)='시')
SET @fie002 = @tmpSTR
ELSE IF(RIGHT(@fie002, 1)='시' AND RIGHT(@tmpSTR, 1)='구')
SET @fie003 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 1)='면')
SET @fie003 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 1)='동')
SET @fie004 = @tmpSTR
ELSE IF(RIGHT(@tmpSTR, 1)='리')
SET @fie004 = @tmpSTR
ELSE IF(CHARINDEX('-', @tmpSTR, 1)>0)
BEGIN
SET @fie006 = SUBSTRING(@tmpSTR, 1, CHARINDEX('-', @tmpSTR, 1) -1)
SET @fie007 = SUBSTRING(@tmpSTR, CHARINDEX('-', @tmpSTR, 1)+1, LEN
(@tmpSTR))
END
ELSE
SET @fie006 = @tmpSTR
SET @STRATINDEX = @ENDINDEX + 1
END
SELECT @fie001 [1], @fie002 [2], @fie003 [3], @fie004 [4], @fie005 [5], @fie006 [6], @fie007 [7]
CLOSE poi1_test
DEALLOCATE poi1_test
이원문이란분이 도와주셔서 진도가 여기까지 나왔습니다.
제가 sql이란걸 배운지가..이제 20일정도 되서리...커서란걸 전혀 모르고 있었습니다.
여기저기 훑어보고 얼추 선언하고 닫고 다 한것 같은데...
결과값이 하나만 나옵니다.
1 2 3 4 5 6 7
서울 강남구 NULL 논현동 NULL 82 1
이렇게만 나오고
poi1이라는 테이블에 있는 다른데이터는 안나오는데...
바로 밑에 질문 올린걸 지금 짜본거거든요...
poi1이란 테이블에 3백만개의 데이터를 분리해야하는데...왜 저거 하나만 나오는 거죠?
제가 뭔가 선언을 잘못한건가요?
아..그리고...계속 묻게 되는데...
마지막 지번부분이 한자리만 나오는데...+2를 해도...-1을 해도...
지번이 80-20인경우는 20이 나와야되는데...2만나와요.. 왜그렇죠..도와주세요~
|
