CREATE procedure [spSplit20]
@Expression varchar(8000),
@Delimeter char(1),
@Ret01 varchar(1000) output,
@Ret02 varchar(1000) output,
@Ret03 varchar(1000) output,
@Ret04 varchar(1000) output,
@Ret05 varchar(1000) output,
@Ret06 varchar(1000) output,
@Ret07 varchar(1000) output,
@Ret08 varchar(1000) output,
@Ret09 varchar(1000) output,
@Ret10 varchar(1000) output,
@Ret11 varchar(1000) output,
@Ret12 varchar(1000) output,
@Ret13 varchar(1000) output,
@Ret14 varchar(1000) output,
@Ret15 varchar(1000) output,
@Ret16 varchar(1000) output,
@Ret17 varchar(1000) output,
@Ret18 varchar(1000) output,
@Ret19 varchar(1000) output,
@Ret20 varchar(1000) output
as
/*
--설명
VB의 Split 함수와 같은 기능을 구현함.
리턴값이 각 요소의 길이이며 Retxx 인수가 각 요소값으로 변환됨.
--예제
declare @len int
declare @r1 int
declare @r2 varchar(1000)
exec @len = spSplit '100x안녕', 'x', @r1 output, @r2 output, '', '', '', '', '', '', '', ''
print 'Len:' + convert(varchar(100), @len) + ', 1:' + convert(varchar(100), @r1) + ', 2:' + @r2
--실제 쓰이는 곳
MDinsertGoodGoodClass, MDinsertPOrder
*/
declare @Length int
declare @PosDelimOld int
declare @PosDelim int
select @Length = 0
select @PosDelimOld = 0
select @PosDelim = 0
--아무 문자열도 없다는 표시로 0을 리턴함.
if(@Expression = '') return 0
--마지막에 구분자가 없다면 붙여줌.
if(right(@Expression, 1) <> @Delimeter) set @Expression = @Expression + @Delimeter
--더이상 열 구분자가 없을 때까지 루프를 돎.
while(@Length < 20) begin
select @PosDelimOld = @PosDelim
--첫째 열의 문자열을 가져옴.(만약 더 이상 구분자가 없다면 빠져나감.)
select @PosDelim = charindex(@Delimeter, @Expression, @PosDelim + 1)
if(@PosDelim = 0) break
select @Length = @Length + 1
if (@Length = 1) begin
set @Ret01 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 2) begin
set @Ret02 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 3) begin
set @Ret03 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 4) begin
set @Ret04 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 5) begin
set @Ret05 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 6) begin
set @Ret06 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 7) begin
set @Ret07 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 8) begin
set @Ret08 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 9) begin
set @Ret09 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 10) begin
set @Ret10 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 11) begin
set @Ret11 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 12) begin
set @Ret12 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 13) begin
set @Ret13 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 14) begin
set @Ret14 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 15) begin
set @Ret15 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 16) begin
set @Ret16 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 17) begin
set @Ret17 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 18) begin
set @Ret18 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 19) begin
set @Ret19 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
if (@Length = 20) begin
set @Ret20 = substring(@Expression, @PosDelimOld + 1, @PosDelim - @PosDelimOld - 1)
continue
end
end --of while
return @Length
