By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,903 Members | 1,670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,903 IT Pros & Developers. It's quick & easy.

MSSQL Replace() for first occurance?

P: 2
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?
Nov 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: 2
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!

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION ReplaceFirst (@origStr varchar(8000), @searchFor varchar(8000), @replaceWith varchar(8000)) 
  2. RETURNS varchar(8000) AS  
  3. BEGIN 
  4.     DECLARE @start AS int
  5.     DECLARE @end AS int
  6.     DECLARE @newStr AS varchar(8000)
  7.  
  8.     SET @start = CHARINDEX(@searchFor, @origStr)
  9.  
  10.     IF (@start = 0)
  11.         BEGIN
  12.             SET @newStr = @origStr
  13.         END
  14.     ELSE
  15.         BEGIN
  16.             SET @end = @start + LEN(@searchFor)
  17.             SET @newStr = SUBSTRING(@origStr, 1, @start - 1) + @replaceWith + SUBSTRING(@origStr, @end, LEN(@origStr) + 1 - @end)
  18.         END
  19.     RETURN @newStr
  20. END
  21.  
Nov 3 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.