--查询select col=replace(replace(col,'"',''),':','') from tb --更新 update tb set col=replace(replace(col,'"',''),':','')
if object_id('t') is not null drop table t gocreate table t(id int,title nvarchar(100))insert into t select 1,'水尼“十万”个为什么' union all select 2,'当代工人:市场化的演变与趋势' union all select 3,'当代画家(东北卷)' union all select 4,'当代画家:“北京篇:;”' if object_id('symbol') is not null drop table symbol go--建立一个标点符号的表,你可以往里面加各种你想替换的标点符号 create table symbol (n nvarchar(10));insert into symbol select '“' union all select '”' union all select ':' union all select ';'go if exists(select * from sys.objects where name = 'fn_replace_symbol') drop function dbo.fn_replace_symbol; gocreate function dbo.fn_replace_symbol(@n nvarchar(1000)) returns nvarchar(1000) as begin declare @i int; declare @count int;set @i = 1 set @count = (select count(*) from symbol);
while @i <= @count begin ;with t as ( select n, row_number() over(order by @@servername) as rownum from symbol )
select @n = replace(@n,(select n from t where rownum = @i),'') set @i = @i + 1 endreturn @n end go--替换效果 select * , dbo.fn_replace_symbol(title) as 替换完后的字符 from t /* id title 替换完后的字符 1 水尼“十万”个为什么 水尼十万个为什么 2 当代工人:市场化的演变与趋势 当代工人市场化的演变与趋势 3 当代画家(东北卷) 当代画家(东北卷) 4 当代画家:“北京篇:;” 当代画家北京篇 */
from tb
--更新
update tb
set col=replace(replace(col,'"',''),':','')
if object_id('t') is not null
drop table t
gocreate table t(id int,title nvarchar(100))insert into t
select 1,'水尼“十万”个为什么' union all
select 2,'当代工人:市场化的演变与趋势' union all
select 3,'当代画家(东北卷)' union all
select 4,'当代画家:“北京篇:;”'
if object_id('symbol') is not null
drop table symbol
go--建立一个标点符号的表,你可以往里面加各种你想替换的标点符号
create table symbol (n nvarchar(10));insert into symbol
select '“' union all
select '”' union all
select ':' union all
select ';'go
if exists(select * from sys.objects where name = 'fn_replace_symbol')
drop function dbo.fn_replace_symbol;
gocreate function dbo.fn_replace_symbol(@n nvarchar(1000))
returns nvarchar(1000)
as
begin
declare @i int;
declare @count int;set @i = 1
set @count = (select count(*) from symbol);
while @i <= @count
begin
;with t
as
(
select n,
row_number() over(order by @@servername) as rownum
from symbol
)
select @n = replace(@n,(select n from t where rownum = @i),'')
set @i = @i + 1
endreturn @n
end
go--替换效果
select * ,
dbo.fn_replace_symbol(title) as 替换完后的字符
from t
/*
id title 替换完后的字符
1 水尼“十万”个为什么 水尼十万个为什么
2 当代工人:市场化的演变与趋势 当代工人市场化的演变与趋势
3 当代画家(东北卷) 当代画家(东北卷)
4 当代画家:“北京篇:;” 当代画家北京篇
*/