SQL 如何从列的值中删除双引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9380570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get rid of double quote from column's value?
提问by User13839404
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
但正在寻找更可靠的解决方案。如果任何列有尾随空格，这将失败
回答by gbn
Just use REPLACE?
只用替换？
...
SET Name = REPLACE(Name,'"', '')
...
回答by Joe Stefanelli
UPDATE Table
SET Name = REPLACE(Name, '"', '')
WHERE CHARINDEX('"', Name) <> 0
回答by Pankaj
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
回答by Jeff Raines
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')
