在我们使用MSSQL的时候,有时表中有一些是全角的数字,但我们的系统不能够正确的使用它们,这时我们要把他们替换为半角数字。而SQL自己好像没有这个函数。
没有,我们自己动手:
下面就是我自己写的一个函数,当然,我的数据量小,没有考虑性能!
-- 把全角数据转换成半角数字(注意,这个只能转换全是全角的,在第一个半角数据时返回)
-- 公司:石家庄创联科技
-- 编写:牛昆亮 QQ:273352165
create
function
ufn_convertWideNumericToAnsi
(
@vstrIn varchar ( 1000 )
)
returns varchar ( 4000 )
as
begin
declare @strReturn varchar ( 4000 )
, @bin varbinary ( 4000 )
, @str varchar ( 4000 )
, @stmp varchar ( 4 )
, @i int
, @len int
-- ,@vstrIn varchar(1000)
-- set @vstrIn ='031851001845'
set @strReturn = ''
set @bin = convert ( varbinary ( 4000 ), @vstrIn )
exec master..xp_varbintohexstr @bin , @str out
select @str = stuff ( @str , 1 , 2 , '' )
set @len = len ( @str )
set @i = 1
while @i < @len
begin
set @stmp = substring ( @str , @i , 4 )
if ( substring ( @stmp , 1 , 1 ) <> ' A ' )
return @vstrIn
set @stmp = replace ( @stmp , ' A ' , '' )
set @stmp = replace ( @stmp , ' B ' , '' )
-- print @stmp
set @stmp = cast (( convert ( int , @stmp ) - 30 ) as varchar ( 1 ))
set @strReturn = @strReturn + @stmp
set @i = @i + 4
end
-- print @strReturn
return @strReturn
end
--示例
select ' 031851001845 ' as ORG, dbo.ufn_convertWideNumericToAnsi( ' 031851001845 ' ) DES
ORG DES
-- ---------------------- ---------------------------
031851001845 031851001845
(
@vstrIn varchar ( 1000 )
)
returns varchar ( 4000 )
as
begin
declare @strReturn varchar ( 4000 )
, @bin varbinary ( 4000 )
, @str varchar ( 4000 )
, @stmp varchar ( 4 )
, @i int
, @len int
-- ,@vstrIn varchar(1000)
-- set @vstrIn ='031851001845'
set @strReturn = ''
set @bin = convert ( varbinary ( 4000 ), @vstrIn )
exec master..xp_varbintohexstr @bin , @str out
select @str = stuff ( @str , 1 , 2 , '' )
set @len = len ( @str )
set @i = 1
while @i < @len
begin
set @stmp = substring ( @str , @i , 4 )
if ( substring ( @stmp , 1 , 1 ) <> ' A ' )
return @vstrIn
set @stmp = replace ( @stmp , ' A ' , '' )
set @stmp = replace ( @stmp , ' B ' , '' )
-- print @stmp
set @stmp = cast (( convert ( int , @stmp ) - 30 ) as varchar ( 1 ))
set @strReturn = @strReturn + @stmp
set @i = @i + 4
end
-- print @strReturn
return @strReturn
end
--示例
select ' 031851001845 ' as ORG, dbo.ufn_convertWideNumericToAnsi( ' 031851001845 ' ) DES
ORG DES
-- ---------------------- ---------------------------
031851001845 031851001845