SQL字符串中取数字难题？
在“sddfd123fddfd56fddf78”中分别取得123   、56     、78   怎么做好，用SQL   语言。

------解决方案--------------------
declare @str varchar(100)
declare @find varchar(10)

set @str= 'sddfd123fddfd56fddf78 '

set @find= '123 '
if charindex(@find,@str)> 0
select substring(@str, charindex(@find,@str), len(@find))

set @find= '56 '
if charindex(@find,@str)> 0
select substring(@str, charindex(@find,@str), len(@find))

set @find= '78 '
if charindex(@find,@str)> 0
select substring(@str, charindex(@find,@str), len(@find))

------解决方案--------------------
--?是不是想把全部数字提取出来?

declare @str varchar(100),@tmp varchar(101),@i int

set @str= 'sddfd123fddfd56fddf78 '
set @tmp=@str+ 'a '

while patindex( '%[0-9]% ',@tmp)> 0
begin
set @i=1
while 1=1
begin
if isnumeric(substring(@tmp,patindex( '%[0-9]% ',@tmp)+@i,1))=0 break
set @i=@i+1
end
print substring(@tmp,patindex( '%[0-9]% ',@tmp),@i)
set @tmp=stuff(@tmp,patindex( '%[0-9]% ',@tmp),@i, ' ')
end

/*
123
56
78
*/
------解决方案--------------------

declare @s varchar(100)
declare @i int
declare @len int
declare @str1 varchar(100)
set @s= 'sddfd123fddfd56fddf78 '
set @str1= ' '
set @len=len(@s)
set @i=1
while @i <=@len
begin
if isnumeric(substring(@s,@i,1))> 0
begin
set @str1=@str1+substring(@s,@i,1)
end
else
begin
set @str1=@str1+ ', '
end
set @i=@i+1
end
select replace(@str1, ', ', ' ')

123 56 78

(1 row(s) affected)
------解决方案--------------------
DECLARE @n int
declare @a table(name varchar(800))
insert @a
select 'sddfd123fddfd56fddf7 '
set @n=1
declare @b table(name varchar(800))
while(@n <=(select len(name) from @a))
begin
insert @b
select case when isnumeric(substring(name,@n,1))=1 then substring(name,@n,1) end

from @a

set @n=@n+1
end
select * from @b where name is not null

name
----------------------------------------------------------------------------------------------------------------
1
2
3
5
6
7

（所影响的行数为 6 行）

------解决方案--------------------


create table #t
(
s varchar(100)
)

insert into #t
select 'dsf121dfds212 ' union all
select 'er875df212x21 ' union all
select '12df5s8s ' union all
select '78879dsf552 ' union all
select '1dsa215sdf522 '

create function aa(@s varchar(100))
returns varchar(100)
as
begin
declare @i int
declare @len int
declare @str1 varchar(100)

set @str1= ' '