字符串截取解决办法_Sql Server

字符串截取

由字符数：
'TYFWLL01 生产部§TYFWLL03 采购部§TYFWLL04 研发部§TYFWLL05 生产技术部'

想得到以下结果：
'YTFWLL01;YTFWLL02;YTFWLL03;YTFWLL04'

------解决方案--------------------
有点麻烦，坐等牛人了。

SQL code

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 macoselect @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 mselect @s1/*YTFWLL01;YTFWLL02;YTFWLL03;YTFWLL04*//*TY 变成了 YT§ 变成了 ; 汉字都被去掉了01 03 04 05 变成了 01 02 03 04*/