http://www.sqler.com/141578 를 잘 이용하시면 가능 합니다.

실력이 부족해서 좀 복잡합니다 ㅡ,ㅡ;;;

참고만 하세요..

------쿼리 시작------

Declare @ll_len decimal(18,0)
Declare @ll_seq Decimal(18,0)

Select @ll_seq = 0

with tb_data(id,cl_data) As
(
     Select 'A','[도급^공동^주식회사코마스^대한민국^17^이태하^]$[대표^공동^네이버시스템(주)^대한민국^43^임병조^]$[도급^공동^주식회사 컴솔텍^대한민국^20^박용희^]$[도급^공동^엔지스인포텍(주)^대한민국^20^유재근^]'
)
,tb_data2 (id,cl_data,seq,leng,l_data) As
(
     Select a.id     As id
           ,SubString(a.cl_data,1,charindex('$',a.cl_data)-1) As cl_data
           ,1        As seq
           ,charindex('$',a.cl_data)+1  As leng
           ,SubString(a.cl_data,charindex('$',a.cl_data)+1,LEN(a.cl_data)) As l_data
       From tb_data a
 
  Union All
 
     Select a.id     As id    
           ,(
                Case When charindex('$',a.l_data) = 0 And IsNull(a.l_data,'') <> ''
                        Then SUBSTRING(a.l_data,1,Len(a.l_data))
                     When charindex('$',a.l_data) <> 0 And IsNull(a.l_data,'') <> ''
                        Then SubString(a.l_data,1,charindex('$',a.l_data)-1)
                End
            )  As cl_data
           ,a.seq + 1   As seq
           ,charindex('$',a.l_data)+1 As leng
           ,(
                Case When charindex('$',a.l_data) = 0 And IsNull(a.l_data,'') <> ''
                        Then ''
                     When charindex('$',a.l_data) <> 0 And IsNull(a.l_data,'') <> ''
                        Then SubString(a.l_data,charindex('$',a.l_data)+1,LEN(a.l_data))
                End
            )
       From tb_data2 a
      Where ISNULL(a.l_data,'') <> ''
)
,tb_data3 (id,seq,l_data) As
(
     Select a.id
           ,a.seq
           ,Replace(REPLACE(a.cl_data,'[',''),']','')
       From tb_data2 a
)     
,tb_data4 (id,seq,seq2,data,leng,l_data) As
(
   Select a.id
         ,a.seq
         ,1
         ,SubString(a.l_data,1,charindex('^',a.l_data)-1)
         ,charindex('$',a.l_data)+1 As leng
         ,SubString(a.l_data,charindex('^',a.l_data)+1,LEN(a.l_data))
     From tb_data3 a
    
 Union All    

   Select a.id
         ,a.seq
         ,a.seq2 + 1
         ,SubString(a.l_data,1,charindex('^',a.l_data)-1)
         ,charindex('$',a.l_data)+1 As leng
         ,SubString(a.l_data,charindex('^',a.l_data)+1,LEN(a.l_data))
     From tb_data4 a
    Where ISNULL(a.l_data,'') <> ''     
)
,tb_data5 (id,seq,data1,data2,data3,data4,data5,data6,rn) As
(
   Select
          a.id
         ,a.seq
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 1
          )
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 2
          )
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 3
          )
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 4
          )
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 5
          )
         ,(
             Select
              Top 1 b.data
               From tb_data4 b
              Where b.id = a.id
                And b.seq = a.seq
                And b.seq2 = 6
          )
         ,ROW_NUMBER() Over ( PARTITION By a.id,a.seq Order By a.id Asc,a.seq Asc)
     From tb_data4 a 
)
    Select a.id
          ,a.seq
          ,a.data1
          ,a.data2
          ,a.data3
          ,a.data4
          ,a.data5
          ,a.data6
      From tb_data5 a
     Where a.rn = 1

----쿼리끝-----

-----결과-----

A 1 도급 공동 주식회사코마스 대한민국 17 이태하
A 2 대표 공동 네이버시스템(주) 대한민국 43 임병조
A 3 도급 공동 주식회사 컴솔텍 대한민국 20 박용희
A 4 도급 공동 엔지스인포텍(주) 대한민국 20 유재근

함수 이용하시는걸 추천 합니다...

오라클 lINSTR 함수기능

--------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE  FUNCTION [dbo].[MssqlINSTR](@OriginalText VARCHAR(8000),@GubunText VARCHAR(100),@Pos INT)

RETURNS VARCHAR(8000)  

BEGIN 

       DECLARE @CleanedText VARCHAR(8000)

       DECLARE @ReplaceText VARCHAR(8000)

       DECLARE @Minus int ,@I INT ,@TempPOS INT ,@NextTempPOS INT

       SET @OriginalText =  @GubunText + @OriginalText + @GubunText

       SET @ReplaceText = REPLACE(@OriginalText ,@GubunText,'')

       SET @Minus = len(@OriginalText) - len(@ReplaceText)

    IF  @Minus - 1 < @Pos

    BEGIN

        RETURN ''

    END

    IF  @Pos < 1

    BEGIN

        RETURN ''

    END

    SET @TempPOS = 0

    SET @I = 0

       WHILE @Pos > @I

       BEGIN

        SET @TempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )

        SET @NextTempPOS = CHARINDEX(@GubunText, @OriginalText , @TempPOS + 1 )

             SET @I = @I + 1

       END 

       RETURN SUBSTRING(@OriginalText ,@TempPOS + 1 ,@NextTempPOS - @TempPOS - 1)   

END

----------------------------------------------------------------------------------------------------------------------------------

with tb_data(id,cl_data) As
(
     Select 'A','[도급^공동^주식회사코마스^대한민국^17^이태하^]$[대표^공동^네이버시스템(주)^대한민국^43^임병조^]$[도급^공동^주식회사 컴솔텍^대한민국^20^박용희^]$[도급^공동^엔지스인포텍(주)^대한민국^20^유재근^]'
) ,
res as
(
SELECT A.id, REPLACE(REPLACE(SUBSTRING(A.cl_data,B.S,B.E-B.S) ,'[',''),']','') VAL , row_number() over (order by  s) idx
  FROM tb_data A
 CROSS APPLY (
 SELECT TOP (2047) NUMBER S
             , CHARINDEX('$',A.cl_data+'$',NUMBER + 1) E
          FROM MASTER.DBO.SPT_VALUES
         WHERE NUMBER = CHARINDEX ('$','$'+A.cl_data,NUMBER)
           AND TYPE ='P'
         ORDER BY NUMBER) B  )
        
select *
     , dbo.MssqlINSTR(a.val,'^',1) data1
     , dbo.MssqlINSTR(a.val,'^',2) data2
     , dbo.MssqlINSTR(a.val,'^',3) data3
     , dbo.MssqlINSTR(a.val,'^',4) data4
     , dbo.MssqlINSTR(a.val,'^',5) data5
     , dbo.MssqlINSTR(a.val,'^',6) data6
  from res a
 
 

xml 파싱하는 방식으로 한번 풀어 보았습니다.

함수를 하나 추가했구요.

create function dbo.fnTbl2NString

( @string xml

) returns table

as

return (

 select row_number() over(order by (select 1)) as rn

 ,col.value('@n','nvarchar(1000)') as value

 from @string.nodes('/x') as a (col)

)

;with cte(id,test) as (

 select 'a',N'[도급^공동^주식회사코마스^대한민국^17^이태하^]$[대표^공동^네이버시스템(주)^대한민국^43^임병조^]$[도급^공동^주식회사 컴솔텍^대한민국^20^박용희^]$[도급^공동^엔지스인포텍(주)^대한민국^20^유재근^]'

)

select a.id

,max(case c.rn when 1 then c.value end) as col1

,max(case c.rn when 2 then c.value end) as col2

,max(case c.rn when 3 then c.value end) as col3

,max(case c.rn when 4 then c.value end) as col4

,max(case c.rn when 5 then c.value end) as col5

,max(case c.rn when 6 then c.value end) as col6

from cte as a

cross apply (

 select rn,replace(replace(value,'[',''),'^]','') as value

 from dbo.fnTbl2NString(convert(xml,'<x n="'+ replace(a.test,'$','"/><x n="') +'"/>'))

) as b

cross apply (

 select rn,value

 from dbo.fnTbl2NString(convert(xml,'<x n="'+ replace(b.value,'^','"/><x n="') +'"/>'))

) as c

group by a.id,b.rn

pivot 버전으로 만들어 보았습니다.

;with cte(id,test) as (

 select 'a',N'[도급^공동^주식회사코마스^대한민국^17^이태하^]$[대표^공동^네이버시스템(주)^대한민국^43^임병조^]$[도급^공동^주식회사 컴솔텍^대한민국^20^박용희^]$[도급^공동^엔지스인포텍(주)^대한민국^20^유재근^]'

)

select id

,[1] as col1

,[2] as col2

,[3] as col3

,[4] as col4

,[5] as col5

,[6] as col6

from (

 select a.id

 , b.rn as groups

 , c.rn

 , c.value

 from cte as a

cross apply (

 select rn,replace(replace(value,'[',''),'^]','') as value

 from dbo.fnTbl2NString(convert(xml,'<x n="'+ replace(a.test,'$','"/><x n="') +'"/>'))

) as b

cross apply (

 select rn,value

 from dbo.fnTbl2NString(convert(xml,'<x n="'+ replace(b.value,'^','"/><x n="') +'"/>'))

) as c

) as x

pivot (

 max(x.value)

 for x.rn in ([1],[2],[3],[4],[5],[6])

) as p