반응형

/********************************************************************************************
-- 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

반응형

+ Recent posts