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.

Answer 1

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

Answer 2

Not tested by it should give some direction:

Update MyTable Set [FieldName]=SUBSTR([FieldName], 2, LEN([FieldName]) WHERE LEFT([FieldName],1) = '"'

And

Update MyTable Set [FieldName]=SUBSTR([FieldName], 1, LEN([FieldName]-1) WHERE RIGHT([FieldName],1) = '"'

Answer 3

Hi

You can try this-

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

cheers