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

Question

Here is the table, each column value is wrapped with double quotes (").

Name    Number      Address Phone1  Fax Value   Status
"Test"  "10000000"  "AB"    "5555"  "555"   "555"   "Active"

How to remove double quote from each column? I tried this for each column:-

UPDATE Table 
SET Name = substring(Name,1,len(Name)-1) 
where substring(Name,len(Name),1) = '"'

but looking for more reliable solution. This fails if any column has trailing white space

you could use TRIM() to get rid of trailing white spaces - i would do it they same way — dom, Feb 21 '12 at 15:53
@Aaron thats correct it doesnt trim double-quotes but as per the last sentence in his question he concerns about the trailing white spaces... — dom, Feb 21 '12 at 16:01
Sorry, missed that. But there is no `TRIM()` in SQL Server. :-) — Aaron Bertrand, Feb 21 '12 at 16:03
SQL Server has `LTRIM()` and `RTRIM()`, just nest them for a full trim: `@result = LTRIM(RTRIM(@source))` — 4AM, Mar 15 '16 at 18:31
Possible duplicate of [Remove Quotation Marks On All Rows in a Column](http://stackoverflow.com/questions/12773275/remove-quotation-marks-on-all-rows-in-a-column) — Nic, Jan 03 '17 at 10:04

score 43 · Accepted Answer · answered Feb 21 '12 at 15:53

43

Just use REPLACE?

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

answered Feb 21 '12 at 15:53

gbn

422,506
82
585
676

But it will replace all the quotes, not only wrapping the column value – Roman Mar 28 '18 at 13:12

score 11 · Answer 2 · answered Feb 21 '12 at 15:52

11

UPDATE Table
    SET Name = REPLACE(Name, '"', '')
    WHERE CHARINDEX('"', Name) <> 0

answered Feb 21 '12 at 15:52

Joe Stefanelli

132,803
19
237
235

Your answer is also good.but then i have to write update tabl..... for each column... whereas without where clause i can update all the columns in one update statement.. Thanks anyways – User13839404 Feb 21 '12 at 18:25

score 0 · Answer 3 · edited Nov 24 '15 at 21:45

Quick and Dirty, but it will work :-) You could expand and write this as a store procedure taking in a table name, character you want to replace, character to replace with, Execute a String variable, etc...

DECLARE 
@TABLENAME VARCHAR(50)

SELECT @TABLENAME = 'Locations'

SELECT 'Update ' + @TABLENAME + ' set ' +  column_Name + ' = REPLACE(' + column_Name + ',''"'','''')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
and data_Type in ('varchar')

score 0 · Answer 4 · answered Feb 21 '12 at 16:01

0

create table #t
(
   Name varchar(100)
)

insert into #t(Name)values('"deded"')
Select * from #t

update #t Set Name = Coalesce(REPLACE(Name, '"', ''), '')
Select * from #t
drop table #t

answered Feb 21 '12 at 16:01

Pankaj

9,749
32
139
283

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

4 Answers4

Linked