How to Remove Leading and Trailing Quotes in SQL Server

How Do I Trim Leading and Trailing Quote from MySQL Row?

Try:

UPDATE `example_table` 
   SET `title` = TRIM(BOTH '"' FROM `title`)

This query will updated your example_table to remove leading and trailing double quotes from the value of the title column.

If you don't want to update the table, but want to fetch the rows with double quotes removed, then use @Sam Dufel's answer.

Remove single quotes in one of the table column in SQl server

I suppose you're just looking for REPLACE()

CREATE View MyData AS
SELECT
'2020/04/20 12:42:05' Value
UNION ALL SELECT '2020/04/22 11:35:58'
UNION ALL SELECT '2020/04/24 08:10:50'
UNION ALL SELECT '2020/04/25 10:06:59'
UNION ALL SELECT '2018-11-29 12:47:23'
UNION ALL SELECT '''2018-12-04 18:36:08'''
UNION ALL SELECT '''2018-12-10 11:33:09'''
UNION ALL SELECT '''2018-12-15 12:33:08''';

SELECT Value, REPLACE(Value, '''', '') OutputValue
FROM MyData;

Here is a db<>fiddle

How to get rid of double quote from column's value?

Just use REPLACE?

...
SET Name = REPLACE(Name,'"', '')
...

stored procedure to remove quotes

CREATE PROCEDURE sp_stripDoubleQuotes 
    @tableName sysname,
    @columnName sysname,
    @SQL varchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;    
    SET @SQL =

    'UPDATE ' + '[' + @tableName +']' +
    'SET' + '[' + @columnName +']' +'= SUBSTRING(' +'[' + @columnName +']' +', 2, LEN(' +'[' + @columnName +']' +'))
    WHERE LEFT('  + '[' + @columnName +']' +', 1) = '+'''"'''
    --PRINT(@SQL)
    EXEC (@SQL)

    SET @SQL =
    'UPDATE ' + '[' + @tableName +']' +
    'SET' + '[' + @columnName +']' +'= SUBSTRING(' + '[' + @columnName +']' +', 1, LEN(' + '[' + @columnName + ']' +')-1)
    WHERE RIGHT(' + '[' + @columnName +']' +', 1) = '+'''"'''
    --PRINT(@SQL)
    EXEC (@SQL)
END
GO
exec [dbo].[sp_stripDoubleQuotes] N'test', N'id'  -- exec [dbo].[sp_stripDoubleQuotes] N'[dbo].[test]', N'[id]' 

Updated 2nd: I added [] to wrap table and column incase your table and column name have whitespace in them. Thanks @Sean Lange and @Richard

Updated 3rd: As @[benjamin moskovits] (xD) mentioned, if you hard coded brackets, the correct execute command is exec [dbo].[sp_stripDoubleQuotes] N'test', N'id'. Try to add or remove brackets and print to see whether the syntax is correct before executing it.

Remove quotation marks from all columns in SQL Server

There isn't a one line script for this but I have a few lines in my code when I get rid of all the double quotes in the stagging table once I have got my data into sql server, mind you all of these columns are varchar data type.

-- Get rid of double quotes in the data

Declare @ColName SYSNAME , @Sql Nvarchar(Max)

Declare Cur Cursor FOR 
SELECT c.name 
from sys.columns c inner join sys.tables t on c.object_id = t.object_id
Where t.name = 'myTable'   --<-- Your Table name

 OPEN Cur 

  FETCH NEXT FROM Cur INTO @ColName

WHILE @@FETCH_STATUS = 0 
BEGIN

      SET @SQL = 'UPDATE myTable
                    SET ' + QUOTENAME(@ColName) + ' = LTRIM(RTRIM(ISNULL(REPLACE(' + QUOTENAME(@ColName) + ' , ''"'' , '''') , '''')))'

      --PRINT @SQL 
      Exec sp_executesql @Sql 

      FETCH NEXT FROM Cur INTO @ColName
END
CLOSE Cur 
DEALLOCATE Cur
GO

---

Related Topics