SQL拆分指定的数据,在将修改拆分的字符串,最后在合并字符串

 

---创建函数

use ddd
go
alter function fun_Cover
(@Name nvarchar(max),@Stuff nvarchar(255))
returns @SplitStringsTable table
(
 ID int identity(1,1),
 Name nvarchar(4000)-- nvarchar(4000)
)
as
begin
 declare @Current_Index int = 0 ---起始位置
 declare @Next_Index int
 declare @Return_Test nvarchar(255) ---字符串
 
 set @Current_Index = 1
 set @Name = rtrim(ltrim(@Name))
 
 /*
     按照逗号(,),分割字符串,分割后在修改数据,最后在合并修改后的数据
 */
 if(charindex(',',@Name) <> 0)
  begin
  while(@Current_Index <= len(@Name))
   begin
   select @Next_Index = charindex(',',@Name,@Current_Index)
   --没有以逗号为分隔符号的数据
   if(@Next_Index =0 or @Next_Index is null)
    begin
    set @Return_Test = substring(@Name,@Current_Index,(charindex(',',@Name)-1))
    set @Return_Test =stuff(@Return_Test,4,0,@Stuff)  ---修改数据
    insert into @SplitStringsTable(Name) values(@Return_Test)
      
    set @Current_Index = len(@Name)+1
    end
    else  --存在以都好为分隔符号的数据
    begin
   set @Return_Test = substring(@Name,@Current_Index,(charindex(',',@Name)-1))
   set @Return_Test =stuff(@Return_Test,4,0,@Stuff)  ---修改数据
   set @Current_Index = @Next_Index +1
   insert into @SplitStringsTable(Name) values(@Return_Test)
    end
    set @Return_Test =''   --循环累加处理好的字符串  
   end
 end
else
 begin
   insert into @SplitStringsTable(Name) values(@Name)
 end 
 
 return;
end
go

 

----调用函数

declare @name nvarchar(255)=''
SELECT @name += name +',' FROM [ddd].[dbo].[fun_Cover] (
   'id=001,id=002,id=003'
  ,'45435345')

select left(@name,LEN(@name)-1)