declare @a varchar(10),@i int select @a='d2sd9sd34',@i=1 select ascii(0),ascii(9)while(@i<=len(@a)) begin set @a=stuff(@a,@i,1,case when ascii(substring(@a,@i,1)) between 48 and 57 then substring(@a,@i,1) else '0' end ) set @i=@i+1 end select cast(@a as bigint)
create function f_str(@var varchar(1000)) returns varchar(1000) as begin declare @ret varchar(1000),@char varchar(2) set @ret='' while len(@var)>0 begin set @char=left(@var,1) set @var=stuff(@var,1,1,'') if isnumeric(@char)=1 and @char!=' ' and @char!='.' set @ret=@ret+@char else set @ret=@ret+'0' end return @ret end go update 表 set 字段=dbo.f_str(字段) go
具体操作: 1 建立函数: create function ichange ( @a varchar(20) )returns bigint as begin declare @i int while(@i<=len(@a)) begin set @a=stuff(@a,@i,1,case when ascii(substring(@a,@i,1)) between 48 and 57 then substring(@a,@i,1) else '0' end ) set @i=@i+1 end return cast(@a as bigint) end调用并更新字段值 update tb set colname=cast(dbo.ichang(colname) as varchar)
select @a='d2sd9sd34',@i=1
select ascii(0),ascii(9)while(@i<=len(@a))
begin
set @a=stuff(@a,@i,1,case when ascii(substring(@a,@i,1)) between 48 and 57 then substring(@a,@i,1) else '0' end )
set @i=@i+1
end
select cast(@a as bigint)
returns varchar(1000)
as
begin
declare @ret varchar(1000),@char varchar(2)
set @ret=''
while len(@var)>0
begin
set @char=left(@var,1)
set @var=stuff(@var,1,1,'')
if isnumeric(@char)=1 and @char!=' ' and @char!='.'
set @ret=@ret+@char
else
set @ret=@ret+'0'
end
return @ret
end
go
update 表 set 字段=dbo.f_str(字段)
go
1 建立函数:
create function ichange
(
@a varchar(20)
)returns bigint
as
begin
declare @i int
while(@i<=len(@a))
begin
set @a=stuff(@a,@i,1,case when ascii(substring(@a,@i,1)) between 48 and 57 then substring(@a,@i,1) else '0' end )
set @i=@i+1
end
return cast(@a as bigint)
end调用并更新字段值
update tb set colname=cast(dbo.ichang(colname) as varchar)