How do I trim these fields of quotation marks in SQL Server?

Question

I have a table with 6 million + records, but the first field has a " at beginning and the last field has " at the end i guess when doing a bulk insert they forgot to remove it. I want to run an update query which will remove the " from those 2 fields Sample data -

Field 1     
"18157142   
"18157152
"18157159
"18157177
"18157189
"18157191
"18157197
"18157208
"18157223
"18157224

Field 2 (Last field)
243901"
832218"
506356"
78301"
753201 - Automobile Body"
553111 - Automobile Parts & Supplies-Retail"
581203"
792903 - Music"
653118"
541105 - Grocers"

Also no place in this field does the " show up anywhere else so its just 1 " to remove from the field.

Any ideas?

Answer 1

This is the update,

update table
set field1 = right(field1,length(field1)-1),
field2 = left(field1,length(field1)-1)

The problem is the enormous transaction that it will generate...

Answer 2

Update myTable 
SET 
    Field1 = Replace(Field1, '"', ''), 
    Field2 = Replace(Field2, '"', '')

This would work faster than other recommendations because you won't have to perform any extra string length function calls to determine how far over to count. This will just replace all occurrences of the quotes, which is what was originally asked for (since quotes won't show up anywhere where they might not want to be removed).

Answer 3

If you have the time to wait I would just:

update [tableName] set field1 = right(field1, len(field1) -1)

update [tableName] set field2 = left(field2, len(field2) -1)

Answer 4

UPDATE TableName
SET Field1 = MID(Field1,2)
    Field2 = MID(Field2,1,LEN(Field2)-1)

Answer 5

UPDATE MyTable
SET Field1 = RIGHT(Field1, LEN(Field1) - 1), Field2 = LEFT(Field2, LEN(Field2) - 1)

Answer 6

UPDATE [table] SET [field1] = RIGHT( [field1], LEN([field1]) - 1), [field2] = LEFT( [field2], LEN([field2]) - 1)