/*
功能说明:字符串替换并去重, 字符串每一项以 @delimiter 隔开
参数说明:@job_type_list 待处理字符串; @delimiter 分割符号, @oldtype 待替换字符串, @newtype 替换的字符串
输出结果说明:
Set @delimiter =','
SET @job_type_list = '123,45'
SET @newtype='13'
SET @oldtype='45'
Return "123,13"
SET @job_type_list = '123,45,13,2,45'
SET @newtype='13'
SET @oldtype='45'
Return "123,13,2"
*/
CREATE FUNCTION [zhaopin].[FN_ReplaceString]
(
@job_type_list varchar(1000),
@delimiter char(1),
@oldtype varchar(100),
@newtype varchar(100)
)
RETURNS varchar(1000)
As
begin
declare @jobtype varchar(1000)
declare @number int
DECLARE @startpos int
if (@job_type_list is NULL)
return null
if (@oldtype is null or @newtype is NULL)
return @job_type_list
SET @job_type_list = replace(@job_type_list, ' ', '')
SET @jobtype = replace(@delimiter + @job_type_list + @delimiter , @delimiter + @oldtype + @delimiter, @delimiter + @newtype + @delimiter)
SET @number = len(replace(@jobtype,@newtype,@newtype+'-'))-len(@jobtype)
IF @number>1
BEGIN
SET @startpos = CHARINDEX(@delimiter + @newtype + @delimiter,@jobtype) + LEN(@delimiter + @newtype + @delimiter)
SET @job_type_list = left(@jobtype,@startpos-1)
SET @jobtype = SUBSTRING(@jobtype, @startpos-1,LEN(@jobtype))
WHILE(CHARINDEX(@delimiter + @newtype + @delimiter,@jobtype)>0)
BEGIN
SET @jobtype = replace(@jobtype,@delimiter + @newtype + @delimiter,',')
END
IF(LEFT(@jobtype, 1) = @delimiter )
SET @jobtype=RIGHT(@jobtype,LEN(@jobtype)-1)
SET @jobtype = @job_type_list + @jobtype
END
IF(len(@jobtype)>2)
SET @jobtype = substring(@jobtype,2,len(@jobtype)-2)
RETURN @jobtype
end
SqlServer字符串替换并去重
最新推荐文章于 2023-05-12 16:16:28 发布