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

Question

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

Solution 1

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)

Solution 2

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