parse a string in a SQL select statement

Question

My SQL is very rusty. I would like write a query to take field1

Field1
----------  
Soap       -AC  
Coffee     -AD

and update it to

Field1
----------  
AC_Soap  
AD_Coffee

There will always be the dash between the 2 parts.

Answer 1

If you're in SQL Server, you'd do it like this:

UPDATE  table
SET     Field1 = SUBSTRING(Field1, CHARINDEX('-', Field1) + 1, LEN(Field1)) +
            '_' + RTRIM(LEFT(Field1, CHARINDEX('-', Field1) - 1))