/********************************************************************************************
-- Title : [SQL2012] 구분자로된 문자열을 10개 필드로 분리
-- Reference : dbrang.tistory.com
-- Key word : 구분자
********************************************************************************************/
create function dbo.fn_ttt(@str nvarchar(500))
returns @return table
( ct1 nvarchar(20), ct2 nvarchar(20), ct3 nvarchar(20), ct4 nvarchar(20), ct5 nvarchar(20)
, ct6 nvarchar(20), ct7 nvarchar(20), ct8 nvarchar(20), ct9 nvarchar(20), ct10 nvarchar(20)
)
as
begin
declare @string nvarchar(500), @space_cnt int;
declare @i1 int, @i2 int, @i3 int, @i4 int, @i5 int, @i6 int, @i7 int, @i8 int, @i9 int, @i10 int
select @string = ltrim(rtrim(@str));
select @space_cnt = len(@string) - len(replace(@string, ' ', ''));
select @i1 = charindex(' ', @string)
, @i2 = case when @i1 = 0 then 0 else charindex(' ', @string, @i1 + 1) end
, @i3 = case when @i2 = 0 then 0 else charindex(' ', @string, @i2 + 1) end
, @i4 = case when @i3 = 0 then 0 else charindex(' ', @string, @i3 + 1) end
, @i5 = case when @i4 = 0 then 0 else charindex(' ', @string, @i4 + 1) end
, @i6 = case when @i5 = 0 then 0 else charindex(' ', @string, @i5 + 1) end
, @i7 = case when @i6 = 0 then 0 else charindex(' ', @string, @i6 + 1) end
, @i8 = case when @i7 = 0 then 0 else charindex(' ', @string, @i7 + 1) end
, @i9 = case when @i8 = 0 then 0 else charindex(' ', @string, @i8 + 1) end
, @i10 =case when @i9 = 0 then 0 else charindex(' ', @string, @i9 + 1) end;
insert into @return
select case when @i1 = 0 then @string
else left(@string, @i1)
end "ct1"
, case when @space_cnt + 1 < 2 then null
when @i2 = 0 then right(@string, len(@string) - @i1)
else substring(@string, @i1 + 1, @i2 - @i1)
end "ct2"
, case when @space_cnt + 1 < 3 then null
when @i3 = 0 then right(@string, len(@string) - @i2)
else substring(@string, @i2 + 1, @i3 - @i2)
end "ct3"
, case when @space_cnt + 1 < 4 then null
when @i4 = 0 then right(@string, len(@string) - @i3)
else substring(@string, @i3 + 1, @i4 - @i3)
end "ct4"
, case when @space_cnt + 1 < 5 then null
when @i5 = 0 then right(@string, len(@string) - @i4)
else substring(@string, @i4 + 1, @i5 - @i4)
end "ct5"
, case when @space_cnt + 1 < 6 then null
when @i6 = 0 then right(@string, len(@string) - @i5)
else substring(@string, @i5 + 1, @i6 - @i5)
end "ct6"
, case when @space_cnt + 1 < 7 then null
when @i7 = 0 then right(@string, len(@string) - @i6)
else substring(@string, @i6 + 1, @i7 - @i6)
end "ct7"
, case when @space_cnt + 1 < 8 then null
when @i8 = 0 then right(@string, len(@string) - @i7)
else substring(@string, @i7 + 1, @i8 - @i7)
end "ct8"
, case when @space_cnt + 1 < 9 then null
when @i9 = 0 then right(@string, len(@string) - @i8)
else substring(@string, @i8 + 1, @i9 - @i8)
end "ct9"
, case when @space_cnt + 1 < 10 then null
when @i10 = 0 then right(@string, len(@string) - @i9)
else substring(@string, @i9 + 1, @i10 - @i9)
end "ct10";
return;
end;
go
select * from dbo.fn_ttt('AA BB CC DD');
go