最近在处理数据清洗方面的工作,有个字段存储的是关联数据的Id,使用的是常规的,
逗号分割的方式。
清洗过程中需要针对该字段中特定的Id进行替换,因为历史的原因,有部分数据存在Id重复的情况,单纯的Replace函数在替换如1,2,3,4,5,5
需要如下步骤:
1. 将字符串转化为 ,1,2,3,4,5,5,
2. 执行Replace函数 REPLACE(',5,',',6,')
3. 去除收尾,
返回执行结果 1,2,3,4,6,5
Replace函数是全部替换的,关键在于,5,
是只存在一个的。
为了数据更加准确、干净,所以设计了如下的函数,执行替换和去重:
CREATE FUNCTION [dbo].[f_replace]
(
-- Add the parameters for the function here
@str varchar(max),
@source varchar(10),
@target varchar(10)
)
RETURNS varchar(max)
AS
begin
if(@str is null or @source is null or @target is null)
return @str;
if(@str='')
return @str;
declare @result varchar(100),@l int,@temp varchar(100);
set @result = ',';
while(CHARINDEX(',',@str)<>0)
begin
set @temp = SUBSTRING(@str,1,CHARINDEX(',',@str)-1)
if(@temp=@source)
Begin
set @temp=@target;
end
if(@temp<>'' and CHARINDEX(','+@temp+',',@result)<=0)
set @result=@result+@temp+',';
set @str=STUFF(@str,1,CHARINDEX(',',@str),'');
end
if(@str=@source)
set @str=@target;
if(@str<>'')
Begin
if(CHARINDEX(','+@str+',',@result)<=0)
set @result=@result+@str+',';
End
set @l=LEN(@result);
set @str = SubString(@result,2,@l-2);
return @str;
end
通过CharIndex和SubString将输入内容进行分割,并一点点构造返回数据(追加数据时,进行数据替换和重复性检测)
可以使用如下测试用例:
select dbo.f_replace('1234,2235,2235,2231,2245,2236','2236','110110');
select dbo.f_replace('1234,2235,2235,2231,2245,2236','2235','110110');
select dbo.f_replace('1234,2235,2235,','2235','110110');
select dbo.f_replace(',1234,2236,','2236','110110');
select dbo.f_replace(',1234,2236,','1234','110110');
select dbo.f_replace('1234,2236,2236','1234','110110');
select dbo.f_replace('1234,2236,2236','2457','110110');
select dbo.f_replace('1234,2236,2236','','110110');
select dbo.f_replace('1234,2236,2236,120','2236','');
select dbo.f_replace('null','2236','');
select dbo.f_replace('12323','','');
select dbo.f_replace('','','');
select dbo.f_replace(null,null,null);