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
– dom, Commented Feb 21, 2012 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
– dom, Commented Feb 21, 2012 at 16:01
Sorry, missed that. But there is no TRIM() in SQL Server. :-) — Aaron Bertrand
– Aaron Bertrand, Commented Feb 21, 2012 at 16:03
SQL Server has LTRIM() and RTRIM(), just nest them for a full trim: @result = LTRIM(RTRIM(@source)) — 4AM
– 4AM, Commented Mar 15, 2016 at 18:31

gbn · Accepted Answer · 2012-02-21 15:53:08Z

44

Just use REPLACE?

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

answered Feb 21, 2012 at 15:53

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roman Over a year ago

But it will replace all the quotes, not only wrapping the column value

Joe Stefanelli · Accepted Answer · 2012-02-21 15:52:59Z

11

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

answered Feb 21, 2012 at 15:52

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

1 Comment

User13839404 Over a year ago

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

Pankaj · Accepted Answer · 2012-02-21 16:01:05Z

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

Tunaki · Accepted Answer · 2015-11-24 21:45:43Z

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')

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Linked

Related