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

Question

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.

question from:https://stackoverflow.com/questions/1066134/how-can-i-remove-leading-and-trailing-quotes-in-sql-server

Answer 1

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