MSSQL Replace() for first occurance?

[hairlessOrphan]

Hi!

Is there any way to get the Replace() function in MSSQL to replace only the first occurence of a substring? For example, my Name column has the following data:

Bob
BobBob

And I want to replace "Bob" with "Robert," but *only* the first occurence:

Robert
RobertBob

Unfortunately, the length of the original string, the searchFor substring, and the replaceWith substring all vary. Any idea what the most efficient way to do this might be?

[hairlessOrphan]

Well, I gave it a shot. I don't know if there's a better solution than this; if you can think of one, please post!

CREATE FUNCTION ReplaceFirst (@origStr varchar(8000), @searchFor varchar(8000), @replaceWith varchar(8000)) 
RETURNS varchar(8000) AS  
BEGIN 
    DECLARE @start AS int
    DECLARE @end AS int
    DECLARE @newStr AS varchar(8000)
 
    SET @start = CHARINDEX(@searchFor, @origStr)
 
    IF (@start = 0)
        BEGIN
            SET @newStr = @origStr
        END
    ELSE
        BEGIN
            SET @end = @start + LEN(@searchFor)
            SET @newStr = SUBSTRING(@origStr, 1, @start - 1) + @replaceWith + SUBSTRING(@origStr, @end, LEN(@origStr) + 1 - @end)
        END
    RETURN @newStr
END