How can I remove leading and trailing quotes in SQL Server?

I have just tested this code in MS SQL 2008 and validated it.

Remove left-most quote:

UPDATE MyTable
SET FieldName = SUBSTRING(FieldName, 2, LEN(FieldName))
WHERE LEFT(FieldName, 1) = '"'

Remove right-most quote: (Revised to avoid error from implicit type conversion to int)

UPDATE MyTable
SET FieldName = SUBSTRING(FieldName, 1, LEN(FieldName)-1)
WHERE RIGHT(FieldName, 1) = '"'

I thought this is a simpler script if you want to remove all quotes

UPDATE Table_Name
SET col_name = REPLACE(col_name, '"', '')

The following script removes quotation marks only from around the column value if table is called [Messages] and the column is called [Description].

-- If the content is in the form of "anything" (LIKE '"%"')
-- Then take the whole text without the first and last characters 
-- (from the 2nd character and the LEN([Description]) - 2th character)

UPDATE [Messages]
SET [Description] = SUBSTRING([Description], 2, LEN([Description]) - 2)
WHERE [Description] LIKE '"%"'

You can use following query which worked for me-

For updating-

UPDATE table SET colName= REPLACE(LTRIM(RTRIM(REPLACE(colName, '"', ''))), '', '"') WHERE...

For selecting-

SELECT REPLACE(LTRIM(RTRIM(REPLACE(colName, '"', ''))), '', '"') FROM TableName

you could replace the quotes with an empty string...

SELECT AllRemoved = REPLACE(CAST(MyColumn AS varchar(max)), '"', ''),
       LeadingAndTrailingRemoved = CASE 
           WHEN MyTest like '"%"' THEN SUBSTRING(Mytest, 2, LEN(CAST(MyTest AS nvarchar(max)))-2)
           ELSE MyTest
           END  
FROM   MyTable

You can use TRIM('"' FROM '"this "is" a test"') which returns: this "is" a test

Try this:

SELECT left(right(cast(SampleText as nVarchar),LEN(cast(sampleText as nVarchar))-1),LEN(cast(sampleText as nVarchar))-2)
  FROM TableName