Tuesday, October 5, 2010

Replace Function

The standard replace() function will replace all occurrence of the string. It will not be possible to replace just the 1st or 2nd occurrence of the string. I have written a replacement for replace() function to do that.

create function fn_replace
(
    @searched    varchar(max),
    @find        varchar(max),
    @replace    varchar(max),
    @nth        int        -- 0 : replace all, non zero : nth occurrance
)
returns    varchar(max)
as
begin
    declare    @output    varchar(max),
        @pos    int,
        @n    int
 
    select    @n = 1
 
    select    @pos = charindex(@find, @searched)
    select    @output = convert(varchar(10), @pos)
 
    while     @n     < @nth
    and    @nth    <> 0
    and    @pos     > 0
    begin
        select    @pos = charindex(@find, @searched, @pos + 1)
        select    @n = @n + 1
    end
 
     select    @output    = case     when @nth = 0    -- replace all
                then replace(@searched, @find, @replace)
                when @pos <> 0
                then stuff(@searched, @pos, len(@find), @replace)
                else @searched
                end
 
    return (@output)
end

No comments:

Post a Comment