SQL字符串中取数字难题？

[liming1019]

在“sddfd123fddfd56fddf78”中分别取得123 、56 、78 怎么做好，用SQL 语言。

[cxmcxm]

基本算法，从开始逐个取字符，用一标志表示取到的字符是否是数字，一直为数字时将取到的字符组合，不是数字时刷新标志，建一临时表保存取到的数字
下面为例子
declare @str varchar(8000),@len int,@num varchar(8000),@ch varchar(1),@i int

declare @tnum table (numcol varchar(8000))
select @str='sddfd123fddfd56fddf78'
set @len=len(@str)
select @i=1
set @num=''
while @i<=@len
begin
select @ch=substring(@str,@i,1)
if @ch in ('1','2','3','4','5','6','7','8','9','0')
begin
set @num=@num+@ch
end else
begin
if @num<>''
begin
insert into @tnum (numcol) values (@num)
set @num=''
end
end
set @i=@i+1

end
if @num<>''
begin
insert into @tnum (numcol) values (@num)
set @num=''
end

select * from @tnum

[dawugui]

--取数字
declare @str varchar(100)
declare @i int,@str1 varchar(10)

set @str='sddfd123fddfd56fddf78' --要分解的字符串
declare @tb table(num varchar(20)) --定义保存结果的表

set @i=patindex('%[^0-9]%',@str)
while @i>0
begin
select @str1=left(@str,@i-1)
,@str=substring(@str,@i,100)
,@i=patindex('%[0-9]%',@str)
,@str=substring(@str,@i,100)
,@i=patindex('%[^0-9]%',@str)
insert into @tb values(@str1)
end
if @str<>'' insert into @tb values(@str)

--显示结果
select * from @tb where num <> ''

/*
num
--------------------
123
56
78
（所影响的行数为 3 行）
*/

[dawugui]

--取字母(假设为a-z)
declare @str varchar(100)
declare @i int,@str1 varchar(10)

set @str='sddfd123fddfd56fddf78'+',' --要分解的字符串
declare @tb table(english varchar(20)) --定义保存结果的表

set @i=patindex('%[^A-Za-z,]%',@str)
while @i>0
begin
select @str1=left(@str,@i-1)
,@str=substring(@str,@i,100)
,@i=patindex('%[A-Za-z,]%',@str)
,@str=substring(@str,@i,100)
,@i=patindex('%[^A-Za-z,]%',@str)
insert into @tb values(@str1)
end
if @str<>'' insert into @tb values(@str)

--显示结果
select * from @tb where english <> ','

/*
english
--------------------
sddfd
fddfd
fddf
（所影响的行数为 3 行）
*/

[nettman]

Mark!

[liming1019]

如果我想数字和非数字同时取，也就是输出结果应该这样：
sddfd
123
fddfd
56
fddf
78
怎么实现，郁闷好几天了？

[sunbird69]

mark

[liming1019]

上面的问题？

[dawugui]

如果我想数字和非数字同时取，也就是输出结果应该这样：
sddfd
123
fddfd
56
fddf
78
怎么实现，郁闷好几天了？

把我的两个合起来分成1,2两个步骤做.

[chuifengde]

declare @a varchar(100),@l int
set @a='sddfd123fddfd56fddf78'
set @l=len(@a)

declare @s table(a varchar(100))
declare @i int
declare @c varchar(100),@n varchar(100)

select @c='',@n=''
set @i=1

while @i<=@l
begin
if isnumeric(left(@a,1))=1
begin
if @c<>''
insert @s select @c
set @n=@n+left(@a,1)
set @a=stuff(@a,1,1,'')
set @c=''
end
else
begin
if @n<>''
insert @s select @n
set @c=@c+left(@a,1)
set @a=stuff(@a,1,1,'')
set @n=''
end
set @i=@i+1
end
if @c<>'' insert @s select @c
if @n<>'' insert @s select @n
select * from @s

[chuifengde]

--result
/*
a
------------------------------
sddfd
123
fddfd
56
fddf
78
*/

[chuifengde]

--or
create function getTable(@v varchar(1000),@flg int)
returns @x table(a varchar(100))
as
begin
declare @t varchar(10)

if @flg=0 set @t='[0-9]' else set @t='[a-z]'
while patindex('%'+@t+'%',@v)>0 set @v=stuff(@v,patindex('%'+@t+'%',@v),1,'|')
while patindex('%||%',@v)>0 set @v=replace(@v,'||','|')

if right(@v,1)='|' set @v=left(@v,len(@v)-1)
if left(@v,1)='|' set @v=right(@v,len(@v)-1)

declare @y table(id int identity(1,1),x int)
insert @y select top 100 1 from syscolumns

insert @x select substring(@v+'|',id,charindex('|',@v+'|',id+1)-id)
from @y
where substring('|'+@v,id,1)='|'
return
end
Go

declare @a varchar(100)
declare @s table(a varchar(100))

set @a='sddfd123fddfd56fddf78'
if isnumeric(@a)=1 or isnumeric(@a)=0 and patindex('%[0-9]%',@a)=0
insert @s select @a
else
begin
insert @s select * from dbo.gettable(@a,0)--数字
insert @s select * from dbo.gettable(@a,1)--字符
end

select * from @s

[ken2002]

学习了

[happy8888]

Mark!!!

[showrock]

学习了...

[gxjgxjgxj]

学习

[lingweixuan]

呵呵，学习了，chuifengde(树上的鸟儿)用的很巧妙，省掉了循环！

[glvicky]

八仙过海，各显神通~

[qlc263]

真厉害~~~~~学习中。。。

[liubo1977]

我用一个SQL加一个输助数表实现了(其中nums为输助数表,大家可以自己建一个):
create table tmp
(a varchar(max) primary key)
go
delete from tmp
insert into tmp
select '1ab34c134ac4b999cd2b5dfd8sdf34e2sfs0666df'
union
select 'df24ac4b999cd2b5dfd8sdf34e2sfs066'
go

select tmp.*,substring(tmp.A,dig_start,dig_end-dig_start+1) from tmp,(
select A,row_number() over(order by A) as rowid,n as dig_end
from tmp
join dbo.nums
on n<=len(A)
and (SUBSTRING(A,n,1) between '0' and '9') and(SUBSTRING(A,n+1,1) not between '0' and '9')
) as T1,(select A,row_number() over(order by A) as rowid,n as dig_start
from tmp
join dbo.nums
on n<=len(A) and (SUBSTRING(A,n,1) between '0' and '9')and SUBSTRING(A,n-1,1) not between '0' and '9' ) as T2

where T1.rowid=T2.rowid and T1.A=T2.A and tmp.A=T1.A

[liubo1977]

我换了一种高效的写法,只需要一个select语句搞定(仍需要辅助数表):
create table #
(A varchar(200))
insert into #
select '1ab34c134ac4b999cd2b5dfd8sdf34e2sfs0666df' as A
union
select 'df24ac4b999cd2b5dfd8sdf34e2sfs066'
go
with t1 as
(
select row_number()over(order by A)as rowid,case when (SUBSTRING(A,n-1,1) not between '0' and '9') then n else 0 end as start_pos,
case when(SUBSTRING(A,n+1,1) not between '0' and '9') then n else 0 end as end_pos--,patindex('%[0-9]%',substring(A,N+1,Len(A)))
from #
join dbo.nums
on n<=len(A)
and (((SUBSTRING(A,n,1) between '0' and '9') and (SUBSTRING(A,n+1,1) not between '0' and '9'))
or((SUBSTRING(A,n,1) between '0' and '9') and (SUBSTRING(A,n-1,1) not between '0' and '9')))
)
select ta.start_pos,tb.end_pos from t1 ta,t1 tb
where ((ta.end_pos =0)and (ta.rowid=(tb.rowid-1)))
or
((ta.start_pos >0) and (ta.end_pos>0)
and(tb.start_pos >0)and(tb.end_pos >0)
and (ta.rowid=tb.rowid))
order by ta.rowid