取记录中最后1个*后的数字

--取记录中最后1个*后的数字

if object_id('tb')is not null drop table tb
go
create TABLE tb(ID varchar(50))
INSERT INTO tb select
'132*345*789ABC' union all select
'dfd*fd*fdf754' union all select
'dfd1*21*fda489(dfda)' union all select
'29*das2*(fda)123'

if object_id('f_str')is not null drop function f_str
go
create function f_str(@id varchar(100))
returns varchar(10)
as
 begin
  declare @str varchar(100),@lenID int ,@len_ int
  set @str=''
  set @lenID=len(@id)---字符串长度
  set @len_=len(@id)-charindex('*',reverse(@id))+2   ---最后一个*后的第一个字符位置

  while @len_<=@lenID
   begin
    if isnumeric(substring(@id,@len_,1))=1
     set @str=@str+substring(@id,@len_,1)
    else
     if @str!='' and isnumeric(substring(@id,@len_,1))=0
      set @len_=@lenID
    set @len_=@len_+1
   end
  return @str
 end
go

select id,number=dbo.f_str(id) from tb 

id                                                 number
-------------------------------------------------- ----------
132*345*789ABC                                     789
dfd*fd*fdf754                                      754
dfd1*21*fda489(dfda)                               489
29*das2*(fda)123                                   123

(4 行受影响)