sql server 拆分字符串，拆分两次（：和；）

 declare @DisciplineID int            
 declare @paramStringVal nvarchar(1000) 
 declare @NPNT nvarchar(100) 
 declare @DisciplineMapping nvarchar(100)
 declare @UEIDCount int
 declare @NPNTDisciplineMapping nvarchar(100)
 declare @rownum2 int
 declare @split nvarchar(1000)
 set @split=';'
 select @paramStringVal=ParamStringVal from   ODM_Parameter where ParameterID='NPNTandDisciplineMapping'

 创建临时表：
  DECLARE @Temp TABLE    
        (    
         value  VARCHAR(100)    
        )   
        
创建临时表：
  DECLARE @Temp1 TABLE    
        (    
         NPNTCode VARCHAR(100)     
        ,DisciplineCode VARCHAR(100)    
        )   

 insert into  @Temp select  B.id AS value 

      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@paramStringVal , @split , '</v><v>')
                            + '</v>')
                ) A
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')
                    FROM    A.[value].nodes('/v') N ( v )
                  ) B
将临时表@Temp拆分后的数据（NPNTCode，DisciplineCode）插入到@Temp1临时表

insert into @Temp1
SELECT 
substring(value,1,patindex('%:%',value)-1) as NPNTCode,
substring(value,patindex('%:%',value)+1,len(value)) as DisciplineCode
from  @Temp 

--NPNTID

  select @DisciplineID=a2.DisciplineID from @Temp1 a
   inner join ODM_CDNPNT a1 on a.NPNTCode=a1.NPNT1 
   inner join ODM_CDDiscipline a2 on a.DisciplineCode=a2.Code1
  where a1.ID=@NPNTID