[MSSQL] T-SQL 문자열 Split 예제

속도와는 전혀 관계없이 가독성 + 기능

DECLARE @voteidx int   

DECLARE @votesave nvarchar(1000)   

DECLARE @Delimiter nvarchar(3)   

DECLARE @LstFlag nvarchar(3)   

 

declare @curLoc int

declare @delLoc int

 

declare @ID int

declare @SubString nvarchar(1000)

 

SET @Delimiter = ','     

 

declare curCon cursor for   

 select conLC, concertID, lstFlag from T_CC_ConcertMst with (nolock) -- where concertid = 72

 

open curCon   

  

fetch next from curCon    

 into  @voteSave, @voteidx, @LstFlag    

   

 

 

while @@fetch_status = 0    

begin

  if @votesave is not null

  begin       

          set @curLoc = 1

          set @votesave = REPLACE(@voteSave, char(10), '')

          set @votesave = REPLACE(@voteSave, char(13), '')

         

          WHILE @curLoc > 0

          BEGIN     

                 set @curLoc =  CHARINDEX(@Delimiter, @voteSave, 1)

       

                 if @curLoc > 0

                 begin

                 begin try

                       set @ID = CONVERT(int, SUBSTRING(@voteSave, 1, @curLoc - 1))

                       set @votesave = SUBSTRING (@voteSave, @curLoc + 1, LEN(@voteSave))

                      

                       INSERT INTO T_CC_Concert_Related_ID (relatedFlag, concertFlag, concertID, relatedID )values('C', @LstFlag, @voteidx, @ID)                      

                 end try

                 begin catch

                       print 'Error :' + convert(varchar(100), @voteidx)

                       goto ERRSPO

                 end catch

                 end  

                 else

                 begin

                       INSERT INTO T_CC_Concert_Related_ID (relatedFlag, concertFlag, concertID, relatedID )values('C', @LstFlag, @voteidx, convert(int, @votesave))        

                       print 'Last Value : '    + convert(varchar(100), @voteSave)     

                 end

          END    

  end

 

ERRSPO:

 

  fetch next from curCon    

     into  @voteSave, @voteidx, @LstFlag    

end   

   

close curCon   

deallocate curCon