sql如何取得一个字段的数字部分？
如一个 字段
1元人民币
人民币1元
取得
1
1
[解决办法]
drop table #t
go
create table #t(a varchar(20))
insert into #t
select '1元人33民2币 '
union all select '人98民6767币1元 '
go
create function uf_getnumber(@a varchar(20))
returns varchar(20)
as
begin
declare @ret varchar(20)
set @ret= ' '
while patindex( '%[0-9]% ',@a)> 0
begin
select @ret=@ret+substring(@a,patindex( '%[0-9]% ',@a),1),
@a=right(@a,len(@a)-patindex( '%[0-9]% ',@a))
end
return @ret
end
go
select dbo.uf_getnumber(a) from #t
/*
--------------------
1332
9867671
（所影响的行数为 2 行）
*/
[解决办法]
set nocount on
declare @str varchar(40)
declare @table table(list varchar(2))
set @str= '人98民6767币1元 '
declare @restr varchar(40),@i int
set @restr= ' '
set @i=1
while(@i <=len(@str))
begin
insert into @table values( substring(@str,@i,1))
set @i=@i+1
end
--select * from @table
declare @substr varchar(2)
declare sub_cursor cursor
for select list from @table
open sub_cursor
fetch next from sub_cursor into @substr
while @@fetch_status = 0
begin
if(@substr> = '0 ' and @substr <= '9 ')
set @restr=@restr+@substr
else
begin
if(substring(@restr,len(@restr)-1,1) != ', ')
set @restr=@restr+ ', '
end
fetch next from sub_cursor into @substr
end
close sub_cursor
deallocate sub_cursor
print stuff(@restr,1,1, ' ')
set nocount off
/*
-----------------------
98,6767,1
----------------------
*/
