在“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 @[email protected]+ 'a '
while patindex( '%[0-9]% ',@tmp)> 0
begin
set @i=1
while 1=1
begin
if isnumeric(substring(@tmp,patindex( '%[0-9]% ',@tmp)[email protected],1))=0 break
set @[email protected]+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 <[email protected]
begin
if isnumeric(substring(@s,@i,1))> 0
begin
set @[email protected]+substring(@s,@i,1)
end
else
begin
set @[email protected]+ ', '
end
set @[email protected]+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 @[email protected]+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= ' '