create function 全半角 (@str Nvarchar(4000),@flag nchar(4)) returns nvarchar(4000) as begin declare @ nvarchar(4000) set @=N'' if @flag=N'全角' while @str<>N'' select @=@+case when unicode(left(@str,1)) between 33 and 126 then nchar(unicode(left(@str,1))+65248) else left(@str,1) end,@str=substring(@str,2,4000) else while @str<>N'' select @=@+case when unicode(left(@str,1)) between 65281 and 65374 then nchar(unicode(left(@str,1))-65248) else left(@str,1) end,@str=substring(@str,2,4000) return @ end goselect dbo.全半角(N'12453ga的sd(a4ss57890','全角')select dbo.全半角(N'12453ga的s(da4ss57890','半角')godrop function 全半角declare @A table(a int identity,b varchar(30)) insert @a select '-=+@@¥@#' insert @a select '-=+@@#' insert @a select '-=+@@¥@#' insert @a select '--=+@@@#' insert @a select '---=+@#¥@@#' insert @a select '---=+@#¥@@@@@@#' select * from @a where b collate Chinese_PRC_CS_AS_KS_WS like '%@@%'a b ----------- ------------------------------ 2 -=+@@# 4 --=+@@@#(所影响的行数为 2 行)
方法1: update table1 set field=replace(field,'(','(')方法2:update table1 set field=dbo.全半角(field,'半角')
returns nvarchar(4000)
as
begin
declare @ nvarchar(4000)
set @=N''
if @flag=N'全角'
while @str<>N''
select @=@+case when unicode(left(@str,1)) between 33 and 126 then nchar(unicode(left(@str,1))+65248) else left(@str,1) end,@str=substring(@str,2,4000)
else
while @str<>N''
select @=@+case when unicode(left(@str,1)) between 65281 and 65374 then nchar(unicode(left(@str,1))-65248) else left(@str,1) end,@str=substring(@str,2,4000)
return @
end
goselect dbo.全半角(N'12453ga的sd(a4ss57890','全角')select dbo.全半角(N'12453ga的s(da4ss57890','半角')godrop function 全半角declare @A table(a int identity,b varchar(30))
insert @a select '-=+@@¥@#'
insert @a select '-=+@@#'
insert @a select '-=+@@¥@#'
insert @a select '--=+@@@#'
insert @a select '---=+@#¥@@#'
insert @a select '---=+@#¥@@@@@@#'
select * from @a where b collate Chinese_PRC_CS_AS_KS_WS like '%@@%'a b
----------- ------------------------------
2 -=+@@#
4 --=+@@@#(所影响的行数为 2 行)
update table1 set field=replace(field,'(','(')方法2:update table1 set field=dbo.全半角(field,'半角')