0

I'm writing a SQL Update statement to replace parts of a string. The one scenario I can't get is to only replace the first part of the string before the colon. I want to make the simple change from the value S to S2. 

Current String: S:S:S;S:S:S
Wanted String: S2:S:S;S:S:S

SQL that I currently have:

UPDATE [table]
    SET [column] = REPLACE([cell], 'S:', 'S2:')
    WHERE [cell] LIKE  'S:%'

This produces the value S2:S2:S;S2:S2:S

Fields in [] are programmatically handled variables. I'm just struggling with the SQL part.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Cody Pickett
  • 11
  • 3
  • You should look into using STUFF. Is the first element always S:? – Sean Lange May 15 '19 at 20:37
  • Thanks for the comment Sean. S, in this case, is only an example. Any string could be passed into the query. However, these queries are being executed by a C# application and I can use that logic to programmatically find the length of the value to be replaced. – Cody Pickett May 15 '19 at 21:29

1 Answers1

2

You see to want STUFF():

UPDATE [table]
    SET [column] = STUFF([cell], 1, 2, 'S2:')
    WHERE [cell] LIKE  'S:%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786