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