How to Modify Query to Remove Double-Quotes from All Columns

How to modify query to remove double-quotes from all columns?

Here is a simple example

CREATE TABLE T
(
  Name    VARCHAR(45),
  Address VARCHAR(45)
);

INSERT INTO T VALUES
('"FirstName"', '"FirstAddress"');

SELECT *
FROM T;

SELECT REPLACE(Name, '"', '') Name,
       REPLACE(Address, '"', '') Address
FROM T;

Or better to use LEFT() and RIGHT() functions as it can be "First"Address" for example, that ensure just the first and last one are removed

SELECT LEFT(RIGHT(Name, LEN(Name) - 1), LEN(Name) - 2) Name,
       LEFT(RIGHT(Address, LEN(Address) - 1), LEN(Address) - 2) Address
FROM T;

Returns

+-------------+----------------+
|    Name     |    Address     |
+-------------+----------------+
| "FirstName" | "FirstAddress" |
+-------------+----------------+


+-----------+--------------+
|   Name    |   Address    |
+-----------+--------------+
| FirstName | FirstAddress |
+-----------+--------------+

---

_{Remember REPLACE() function will remove any " found.}

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

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

Just use REPLACE?

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

Remove quotes "..." from all columns with AWK

If there are no double quotes you want to preserve, simply

awk 'BEGIN { FS=","; OFS="\t" } { gsub("\"", "") } { $1=$1 } 1' file.csv >file.tsv

Incidentally, this also demonstrates how to quote a literal double-quote character inside a double-quoted string; backslash-escape it.

If you only want to remove double quotes around some columns, you can limit the scope of the gsub. For example,

... { gsub("\"", "", $1); gsub("\"", "", $3) } 1

to only manipulate columns 1 and 3. Then you can drop the { $1 = $1 } because we are forcing Awk to recalculate its columns anyway (so the FS gets replaced with OFS).

To not manipulate the first line (which commonly contains the header, not data), add a condition:

... NR>1 { gsub(...) }1

The "replace everywhere" can be expressed quite succinctly in sed, though it tends to be more of a write-only language.

sed -e 's/,/\t/g' -e 's/"//g' file.csv >file.tsv

Not all sed dialects interpret \t as a literal tab, and some are even allergic to multiple -e options; but this should work fine on Ubuntu and generally Linux (other than possibly Busybox and other similar attempts to relive the constraints of the jolly 1970s).

---

Related Topics