Remove quotation marks from all columns in SQL Server

Question

I have a table with three columns: A, B and C.

A ; B; C
"a1"; "b1"; "c1"
"a2"; "b2"; "c3"
"a3"; "b3"; "c3"

I need to remove the quotation marks from all rows in the table. In this post I found a solution which works but requires to specify the names of all columns:

UPDATE myTable
SET A  = REPLACE(A, '"', '');

UPDATE myTable
SET B  = REPLACE(B, '"', '');

UPDATE myTable
SET C  = REPLACE(C, '"', '');

QUESTION: Is there a less verbose way to apply the replace to all columns? For example a one-line expression?

Thanks

The answer is no. But if only some of the rows have those quotation marks, you could add `where a like '%"%' or b like ...` to keep transaction size down (and avoid trigger actions, if any.) — jarlh, Jan 03 '17 at 10:14

score 5 · Accepted Answer · answered Jan 03 '17 at 10:17

5

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

answered Jan 03 '17 at 10:17

M.Ali

67,945
13
101
127

1

Would be easier to write the replace once for each column, unless there are way too many of them... – Zohar Peled Jan 03 '17 at 10:21
@ZoharPeled Well I have a dynamic number of columns in each upload ranging from 30 - 150 in some cases, this suits me best. – M.Ali Jan 03 '17 at 10:25
In that case I have to agree, however I'm not so sure it's the same case for the OP. nowhere in the question is there even a hint of such a situation... – Zohar Peled Jan 03 '17 at 10:27
Thanks @M.Ali, I appreciate the effort. As a matter of fact, it takes quite a lot of lines, but I believe this is something SQL is not designed for. – Nic Jan 03 '17 at 10:38

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

If number of records are not huge:-

You can script out the schema and data.
Replace the double quotes by Find and Replace All.
Run the cleaned script.

edited May 23 '17 at 12:24

Community

1
1

answered Jan 03 '17 at 10:42

p2k

2,126
4
23
39

score 0 · Answer 3 · answered Jan 03 '17 at 12:26

The below procedure will replace any single character with another single character in any table :)

USE [TSQL2012]--your database name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.replace_char 
@char_to_replace char(1), --character to be replaced
@expected_char char(1),
@table_name nvarchar(128) --your table name 
AS 
BEGIN

--Variable declaration
DECLARE @Column_Count_1 int 
DECLARE @SQLString AS NVARCHAR(4000)
DECLARE @count int=1
DECLARE @column_name nvarchar(128)

--Getting the count of column
SET @SQLString=N'select @Column_Count=count(*)  from  '+ @table_name
EXEC sp_executesql  @SQLString
, N'@Column_Count int OUTPUT'
, @Column_Count = @Column_Count_1 OUTPUT

--Getting the actual column names into a temporary table
select  c.name as name
into #temp_column_names
from sys.tables t
join sys.columns c
on t.object_id=c.object_id
where t.name=@table_name

--Looping through each column to replace the required character
WHILE (@count<=@Column_Count_1)
BEGIN
--setting the column name
SET @column_name=(select top 1 name from 
    (select Row_number()over (order by name) as r_n_n, name                  
    from #temp_column_names) aa 
    where r_n_n >=@count)

--updating the rows
SET @SQLString =N'Update '  + @table_name
SET @SQLString= @SQLString + N' Set ' + @column_name
SET @SQLString= @SQLString + N' = replace('  + @column_name
SET @SQLString =@SQLString + N',''' +@char_to_replace
SET @SQLString=@SQLString + N''',''' +@expected_char
SET @SQLString=@SQLString + N''');'

EXEC(@SQLString); 

SET @count=@count+1;
END

--Dropping the temp table
DROP TABLE #temp_column_names

END
GO

Execution of the above procedure

EXEC dbo.replace_char @char_to_replace, @expected_char, @table_name

In your case

EXEC dbo.replace_char '"', '','Sample_1'

Sample_1 is the table which I created.

Remove quotation marks from all columns in SQL Server

3 Answers3