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

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:


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


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)
  8.     SET @start = CHARINDEX(@searchFor, @origStr)
  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
Nov 3 '06 #2

Post your reply

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