第一步、手动创建一个函数：
CREATE FUNCTION [dbo].[ReplaceFirst]
(@source nvarchar(max), @find nvarchar(1000), @repl nvarchar(1000),@CharStr nvarchar(100)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @ResultVar nvarchar(max) -- return value
DECLARE @Pos int -- find the first position
if(dbo.IsNullOrEmpty(@CharStr) = 1)
begin
set @Pos = CHARINDEX(@find, @source)
-- not found, return orginal
if @Pos = 0 return @source
set @ResultVar = stuff(@source, @Pos, len(@find), @repl)
end
else
begin
DECLARE @Pos2 int,@Pos3 int
set @Pos = CHARINDEX(@find+@CharStr, @source)
set @Pos2 = CHARINDEX(@CharStr+@find, @source)
set @Pos3 = CHARINDEX(@find, @source)
-- not found, return orginal
if @Pos > 0
set @ResultVar = stuff(@source, @Pos, len(@find+@CharStr), @repl)
else if @Pos2 > 0
set @ResultVar = stuff(@source, @Pos2, len(@CharStr+@find), @repl)
else if @Pos3 > 0
set @ResultVar = stuff(@source, @Pos3, len(@find), @repl)
else
return @source
end
-- Return the result of the function
RETURN @ResultVar
END
第二步、直接调用：
select [dbo].[ReplaceFirst]('aaaa;bbbb;aaaa;cccc','aaaa','',';');
结果：bbbb;aaaa;cccc
