本节内容:
sql server 字段中全角数字与半角数字的转换。
一,建立函数
复制代码 代码示例:
create function changestr (@s varchar(100))
returns varchar(100) as
--字符串中的全角数字转换为半角数字
begin
set @s = replace(@s,'0','0')
set @s = replace(@s,'1','1')
set @s = replace(@s,'2','2')
set @s = replace(@s,'3','3')
set @s = replace(@s,'4','4')
set @s = replace(@s,'5','5')
set @s = replace(@s,'6','6')
set @s = replace(@s,'7','7')
set @s = replace(@s,'8','8')
set @s = replace(@s,'9','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('132610593')
select dbo.changestr('132610593**')