Updating the left side of a string up to a delimiter

Question

My column "ColumnOne" in my table "MyTable" has values like this: Delimiter is character '-'

|Something                  |   
|Something - SomeOtherThing |
|Something - SomethingElse  |   
|Something - Whatever       |
|OtherThing -               |

I want to update the values so eventually it look like this:

|Something                  |   
|           SomeOtherThing  |
|           SomethingElse   |   
|           Whatever        |
|                           |

So basically algorithm being to replace with white space and keep going until you see '-' , replace that too also with whitespace.

I tried the REPLACE command to say like UPDATE MyTable SET ColumnOne = REPLACE(ColumnOne, ' - ', ' ' + ColumnOne) but that's wrong. I couldn't figure out the pattern for its second argument. Any suggestions are appreciated.

Answer 1

Use charindex to find the amount of characters to change, stuff to perform the change, and replicate to generate a string of N spaces. Try this:

stuff(ColumnOne,1,charindex('-',ColumnOne),replicate(' ',charindex('-',ColumnOne))