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

sql, sql-server

Solution

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) = '"'

Problem

I have a table in a SQL Server database with an NTEXT column. This column may contain data that is enclosed with double quotes. When I query for this column, I want to remove these leading and trailing quotes. For example: "this is a test message" should become this is a test message I know of the LTRIM and RTRIM functions but these workl only for spaces. Any suggestions on which functions I can use to achieve this.

Original source