字段中的全角数字转换为半角数字

建立函数
create function changestr (@s varchar(100))
returns varchar(100) as
--字符串中的全角数字转换为半角数字
begin
set @s = replace(@s,'０','0')
set @s = replace(@s,'１','1')
set @s = replace(@s,'２','2')
set @s = replace(@s,'３','3')
set @s = replace(@s,'４','4')
set @s = replace(@s,'５','5')
set @s = replace(@s,'６','6')
set @s = replace(@s,'７','7')
set @s = replace(@s,'８','8')
set @s = replace(@s,'９','9')
return @s
end
go
----------------------------------------------
create function changenum(@str nvarchar(4000))
returns decimal
--全角数字转换为半角数字
as
begin
declare @tmp nvarchar(4000)
while len(@str)>0
begin
select @tmp=isnull(@tmp,'')+nchar(unicode(left(@str,1))-65248)
select @str=stuff(@str,1,1,'')
end
return @tmp
end
go
----------------------------------------------
select dbo.changenum('１３２６１０５９３')
select dbo.changestr('１３２６１０５９３**')