问题:

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

回答1:

Just use REPLACE?

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

回答2:

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

回答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

回答4:

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