How to get rid of double quote from column value?

Question

Here is a table, each column value is wrapped with double quotation marks (").

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 a more reliable solution. This fails if any column has a space

Answer 1

Just use REPLACE?

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

Answer 2

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

Answer 3

 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 

Answer 4

Quick and dirty, but it will work :-) You can expand and write it as a storage procedure using the table name, the character you want to replace, the character you want to replace, execute the 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')