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 intset @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 nullname ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 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,',',' ') endselect 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
基本算法,从开始逐个取字符,用一标志表示取到的字符是否是数字,一直为数字时将取到的字符组合,不是数字时刷新标志,建一临时表保存取到的数字 下面为例子 declare @str varchar(8000),@len int,@num varchar(8000),@ch varchar(1),@i intdeclare @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='' endselect * 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 行) */
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=1while @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
--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 Godeclare @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)--字符 endselect * from @s
我用一个SQL加一个输助数表实现了(其中nums为输助数表,大家可以自己建一个): create table tmp (a varchar(max) primary key) go delete from tmp insert into tmp select '1ab34c134ac4b999cd2b5dfd8sdf34e2sfs0666df' union select 'df24ac4b999cd2b5dfd8sdf34e2sfs066' goselect 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
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))
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 @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 nullname
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
2
3
5
6
7(所影响的行数为 6 行)
(
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,',',' ')
endselect dbo.aa(s) from #t 121 212
875 212 21
12 5 8
78879 552
1 215 522(5 row(s) affected)
@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 行受影响)
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 行受影响)
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('%[A-Za-z]%')
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('%[A-Za-z]%')--------非数字不一定就是大小写字母。
下面为例子
declare @str varchar(8000),@len int,@num varchar(8000),@ch varchar(1),@i intdeclare @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=''
endselect * 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 行)
*/
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 行)
*/
sddfd
123
fddfd
56
fddf
78
怎么实现,郁闷好几天了?
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=1while @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
/*
a
------------------------------
sddfd
123
fddfd
56
fddf
78
*/
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
Godeclare @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)--字符
endselect * from @s
sddfd
123
fddfd
56
fddf
78
怎么实现,郁闷好几天了?把我的两个合起来分成1,2两个步骤做.
create table tmp
(a varchar(max) primary key)
go
delete from tmp
insert into tmp
select '1ab34c134ac4b999cd2b5dfd8sdf34e2sfs0666df'
union
select 'df24ac4b999cd2b5dfd8sdf34e2sfs066'
goselect 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
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