Updating the left side of a string up to a delimiter-CodePudding

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.

CodePudding user response：

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))