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= ' '
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
return replace(@str1, ', ', ' ')
end
select dbo.aa(s) from #t
121 212
875 212 21
12 5 8
78879 552
1 215 522
(5 row(s) affected)
[解决办法]
create procedure pro
@str varchar(200)
as
begin
create table #t (t1 varchar(20))
declare @tmp varchar(20)
declare @c varchar(1)
set @tmp= ' '
set @c= ' '
while (len(@str)> 0)
begin
set @c=SUBSTRING(@str,1,1)
set @str=SUBSTRING(@str,2,len(@str)-1)
if (@c> = '0 ' and @c <= '9 ' )
set @tmp=@tmp+@c
else
begin
if(len(@tmp) <> 0)
begin
insert #t(t1) values(@tmp)
end
set @tmp= ' '
end
end
if(len(@tmp) <> 0)
begin
insert #t(t1) values(@tmp)
end
select * from #t
end
go
-----------
exec pro 'sddfd123fddfd56fddf78 '
drop procedure pro
---------------------------
t1
123
56
78
------------
(3 行受影响)
[解决办法]
改成int型的
create procedure pro
@str varchar(200)
as
begin
create table #t (t1 int)
declare @tmp varchar(20)
declare @c varchar(1)
set @tmp= ' '
set @c= ' '
while (len(@str)> 0)
begin
set @c=SUBSTRING(@str,1,1)
set @str=SUBSTRING(@str,2,len(@str)-1)
if (@c> = '0 ' and @c <= '9 ' )
set @tmp=@tmp+@c
else
begin
if(len(@tmp) <> 0)
begin
insert #t(t1) values(Convert(int,@tmp))
end
set @tmp= ' '
end
end
if(len(@tmp) <> 0)
begin
insert #t(t1) values(Convert(int,@tmp))
end
select * from #t
end
go
-----------
exec pro 'sddfd123fddfd56fddf78 '
drop procedure pro
---------------------------
t1
123
56
78
------------
(3 行受影响)
[解决办法]
declare @str varchar(1000)
set @str= 'sadsad3s323asd234s '
declare @num varchar(1000)
set @num= ' '
declare @size int
set @size=0
while len(@str)!=@size
begin
set @size=len(@str)
set @num=@num+substring(@str,patindex( '%[0-9]% ',@str),1)
set @str=right(@str,len(@str)-patindex( '%[0-9]% ',@str))
end
select @num
[解决办法]
patindex( '%[0-9]% ')
变为
patindex( '%[A-Za-z]% ')
[解决办法]
那么如果我想取非数字的字符串呢？
---------------------------
declare @str varchar(100),@i varchar(2)
set @str= 'sddfd123fddfd56fddf78 '
set @i = 0
while @i <=9 select @str=replace(@str,@i, ', '),@i=@i+1
while charindex( ',, ',@str)> 0 set @str=replace(@str, ',, ', ', ')
if right(@str,1)= ', ' set @str=left(@str,len(@str)-1)
print @str
[解决办法]
patindex( '%[0-9]% ')
变为
patindex( '%[A-Za-z]% ')
--------
非数字不一定就是大小写字母。
[解决办法]
基本算法，从开始逐个取字符，用一标志表示取到的字符是否是数字，一直为数字时将取到的字符组合，不是数字时刷新标志，建一临时表保存取到的数字
下面为例子
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
[解决办法]
--取数字
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 行）
*/
[解决办法]
--取字母(假设为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 行）
*/
[解决办法]
Mark!
[解决办法]
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
[解决办法]
--result
/*
a
------------------------------
sddfd
123
fddfd
56
fddf
78
*/
[解决办法]
--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
[解决办法]
mark
[解决办法]
如果我想数字和非数字同时取，也就是输出结果应该这样：
sddfd
123
fddfd
56
fddf
78
怎么实现，郁闷好几天了？
把我的两个合起来分成1,2两个步骤做.
[解决办法]
学习了
[解决办法]
Mark!!!
[解决办法]
呵呵，学习了，chuifengde(树上的鸟儿)用的很巧妙，省掉了循环！
[解决办法]
八仙过海，各显神通~
[解决办法]
学习了...
[解决办法]
学习
[解决办法]
真厉害~~~~~学习中。。。
[解决办法]
我用一个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
[解决办法]
我换了一种高效的写法,只需要一个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
