SELECT LEFT(REPLACE(REPLACE(REPLACE(REPLACE('TYFWLL01 生产部§TYFWLL03 采购部§TYFWLL04 研发部§TYFWLL05 生产技术部' + '§',' 生产部§',';'),' 研发部§',';'),' 采购部§',';'),' 生产技术部§',';'),LEN(REPLACE(REPLACE(REPLACE(REPLACE('TYFWLL01 生产部§TYFWLL03 采购部§TYFWLL04 研发部§TYFWLL05 生产技术部' + '§',' 生产部§',';'),' 研发部§',';'),' 采购部§',';'),' 生产技术部§',';')) - 1)

------解决方案--------------------
SQL code

/*
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            set @c = stuff(@c,1,charindex(@split,@c),'')   
        end   
      insert @t(col) values (@c)   
      return   
end
*/
declare @s varchar(80)
set @s='TYFWLL01 生产部§TYFWLL03 采购部§TYFWLL04 研发部§TYFWLL05 生产技术部'

declare @s1 varchar(80) 
;with maco as (
select row_number() over (order by getdate()) as id, * from [dbo].[m_split](@s,'§')
)
select @s1=coalesce(@s1+';','')+
left(col,charindex(' ',col)) from maco
select @s1
/*
TYFWLL01 ;TYFWLL03 ;TYFWLL04 ;TYFWLL05 
*/

------解决方案--------------------
SQL code

/*
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))   
  returns @t table(col varchar(200))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            set @c = stuff(@c,1,charindex(@split,@c),'')   
        end   
      insert @t(col) values (@c)   
      return   
end
*/
declare @s varchar(80)
set @s='TYFWLL01 生产部§TYFWLL03 采购部§TYFWLL04 研发部§TYFWLL05 生产技术部'
declare @s1 varchar(80) 

;with maco as (
select row_number() over (order by getdate()) as id, * from [dbo].[m_split](@s,'§')
),m as(select left(col,charindex(' ',col)-2)+ltrim(id) as col from maco)
select @s1=coalesce(@s1+';','')+'YT'+right(col,len(col)-2) from m

select @s1
/*
YTFWLL01;YTFWLL02;YTFWLL03;YTFWLL04
*/

/*
TY 变成了 YT
§ 变成了 ; 
汉字都被去掉了
01 03 04 05 变成了 01 02 03 04
*/