SQL Server Right() function to remove characters from certain point on column

I'm trying to remove text from my Description column after the "-" character. I know I would have to use the Right() function, but what would the syntax look like to loop through all the records and change all the r开发者_如何学Pythonecords in the Description column?

Example...

BEFORE:

0002    55  Actor Hill - 0002
0004    57  Adair - 0004
0005    74  Adams - 0005

AFTER:

0002    55  Actor Hill 
0004    57  Adair 
0005    74  Adams 

---

try this in SQL Server:

DECLARE @String varchar(50)
SET @String='0002 55 Actor Hill - 0002'
SELECT LEFT(@String,CHARINDEX(' - ',@String)-1)

OUTPUT:

--------------------------------------------------
0002 55 Actor Hill

(1 row(s) affected)

here is an example using a table:

DECLARE @YourTable table (RowValue varchar(50))
INSERT @YourTable VALUES ('0002 55 Actor Hill - 0002')
INSERT @YourTable VALUES ('0004 57 Adair - 0004')
INSERT @YourTable VALUES ('0005 74 Adams - 0005')

SELECT 
    LEFT(RowValue,CHARINDEX(' - ',RowValue)-1), '|'+LEFT(RowValue,CHARINDEX(' - ',RowValue)-1)+'|'
    FROM @YourTable

OUTPUT:

-------------------- ---------------------
0002 55 Actor Hill   |0002 55 Actor Hill|
0004 57 Adair        |0004 57 Adair|
0005 74 Adams        |0005 74 Adams|

(3 row(s) affected)

I used the | characters to show there are no trailing spaces

to change the actual data use:

UPDATE @YourTable
    SET RowValue=LEFT(RowValue,CHARINDEX(' - ',RowValue)-1)

---

Assuming that the contents of the 1st column is what you want to remove from the last column

UPDATE
   Mytable
SET
   StringField3 = REPLACE(StringField3, ' - ' + NumberLikeField1, '')

This ways, you don't have worry about values like

00007    99  Saxa-Cotburg-Gotha - 00007