如果有三个[]时,分别怎样显示? 针对楼主有两个[]时的列子需要用函数拆分:create table ta(id int,num varchar(20)) insert ta select 1, '870[0-9][0-9]' insert ta select 2, '871[478][0-9]' insert ta select 3, '890[478]00' insert ta select 4, '890900' gocreate function test_f(@num varchar(50) ) returns varchar(1000) as begin declare @i int,@j int,@r int,@split varchar(20),@s varchar(1000),@a int,@b int,@name varchar(20),@name1 varchar(20) declare @tb table(id int)--辅助表变量 insert @tb select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9declare @ta table (con int identity(1,1),name varchar(1000))--返回值表变量 set @r=0 select @a=0,@b=0,@name=@num,@name1=@num while charindex('-',@name)>0 select @name=stuff(@name,charindex('-',@name),1,''),@a=@a+1 while charindex('[',@name1)>0 select @name1=stuff(@name1,charindex('[',@name1),1,''),@b=@b+1while charindex('[',@num)>0 begin set @r=@r+1 select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1) if @r=1 begin
if charindex('-',@num) between @i and @j insert @ta select stuff(@num,@i,@j-@i+1,substring(@num,@i+1,1)) union all select stuff(@num,@i,@j-@i+1,substring(@num,@j-1,1)) else insert @ta select stuff(@num,@i,@j-@i+1,id) from @tb where id like @split end else begin if charindex('-',@num) between @i and @j insert @ta select stuff(name,@i,@j-@i+1,min(id)) from @tb,@ta where id like @split group by name union all select stuff(name,@i,@j-@i+1,max(id)) from @tb,@ta where id like @split group by name else insert @ta select stuff(name,@i,@j-@i+1,id) from @tb,@ta where id like @split end set @num=stuff(@num,@i,@j-@i+1,0) end if @a=2 select @s=min(name)+'-'+max(name) from @ta where name not like '%[[]%' else if @a+@b=1 select @s=isnull(@s+' ','')+name from @ta where name not like '%[[]%' else if @a+@b=0 set @s=@num else select @s=isnull(@s+' ','')+min(b.name)+'-'+max(b.name) from (select * from @ta where name like '%[[]%')a join (select * from @ta where name not like '%[[]%')b on b.name like a.name group by a.namereturn @sendgo select id,num=dbo.test_f(num) from taid num ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 87000-87099 2 87140-87149 87170-87179 87180-87189 3 890400 890700 890800 4 890900(所影响的行数为 4 行)--drop function test_f --drop table ta
lisa_g() ( ) 信誉:100 Blog 加为好友 2007-04-26 17:32:29 得分: 0
playwarcraft:你好. 如果有patindex()来算,有没有具体的算法啊.谢谢
同样需要表辅助
--刚写了一个例子,8[14-9][0-9][0-9]这条记录生成的是三条范围,没做更改,下班了。。 楼主参照一下~~ create table ta(id int,num varchar(20)) insert ta select 1, '870[0-9][0-9]' insert ta select 2, '871[478][0-9]' insert ta select 3, '890[478]00' insert ta select 4, '890900' insert ta select 5, '8[789][0-9][0-9]' insert ta select 6, '8[14-9][0-9][0-9]' go alter function test_f(@num varchar(50)) returns varchar(1000) as begin declare @i int,@j int,@r int,@split varchar(20),@s varchar(1000),@a int,@b int, @c int,@name varchar(20),@name1 varchar(20),@name2 varchar(20) declare @ta table (con int identity(1,1),name varchar(1000),level varchar(1000))--返回值表变量 select @a=0,@b=0,@c=0,@name=@num,@name1=@num,@name2=@num while charindex('-',@name)>0 select @name=stuff(@name,charindex('-',@name),1,''),@a=@a+1 while charindex('[',@name1)>0 select @name1=stuff(@name1,charindex('[',@name1),1,''),@b=@b+1if charindex('[',@num)!>0 return @numwhile charindex('[',@num)>0 begin set @c=@c+1 select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)while patindex('%[^0-9]%',@split)>0 set @split=stuff(@split,patindex('%[^0-9]%',@split),1,'')set @r=len(@split)if @c=1 while @r>0 and @c=1 begin insert @ta(name) select stuff(@name2,@i,@j-@i+1,substring(@split,@r,1)) set @r=@r-1 end else if @c=2 begin while @r>0 begin insert @ta select stuff(name,@i,@j-@i+1,substring(@split,@r,1)),level=name from @ta set @r=@r-1 end end else insert @ta select stuff(name,@i,@j-@i+1,substring(@split,@r,1)),name from @ta select @num=stuff(@num,@i,@j-@i+1,0)end declare @tb table(id int identity(1,1),name varchar(20),level varchar(20)) insert @tb select name,isnull(min(level),null) from @ta a where con>(select isnull(max(con),0) from @ta where patindex('%[[]%',name)>0) group by name order by nameif (select max(id)%2 from @tb)=0 select @s=isnull(@s+' ','')+ name+'-'+(select top 1 name from @tb where id%2=0 and id>a.id order by id) from @tb a where id%2=1 else select @s=isnull(@s+' ','')+name from @tb return @s end go select id,num=dbo.test_f(num) from ta id num ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 87000-87009 87090-87099 2 87140-87149 87170-87179 87180-87189 3 890400 890700 890800 4 890900 5 8709-8799 8809-8899 8909-8999 6 8109-8199 8409-8499 8909-8999(6 行受影响)
alter function test_f(@num varchar(50))--把alter 改为create
写这样的语句比较耗时间,上班时间不可能有时间写: 这里写了一个思路,楼主看看问题有没有解决。 create table ta(id int,num varchar(20)) insert ta select 1, '870[0-9][0-9]' insert ta select 2, '871[478][0-9]' insert ta select 3, '890[478]00' insert ta select 4, '890900' insert ta select 5, '8[789][0-9][0-9]' insert ta select 6, '8[14-9][0-9][0-9]' go create function test_f(@num varchar(50)) returns varchar(1000) as begin declare @i int,@j int,@r int,@a int,@b int, @split varchar(20),@split1 varchar(20),@split2 varchar(20), @name varchar(20),@min_name varchar(20),@max_name varchar(20),@s varchar(1000) declare @ta table (id int identity(1,1),min_name varchar(20),max_name varchar(20),level int,tag int)--返回值表变量select @r=0,@name=@numselect @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1) if charindex('[',@num)=0 return @num while charindex('[',@num)>0 begin set @r=@r+1 select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)if @r=1 begin if charindex('-',@num) between @i and @j begin select @split1=reverse(substring(@split,2,charindex('-',@split)-2)), @split2=reverse(substring(@split,charindex('-',@split)+1,len(@split)-charindex('-',@split)-1)), @a=case when len(@split1)>len(@split2) then len(@split1) else len(@split2) end while @a>0 begin select @min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end, @max_name=case when substring(@split2,@a,1)='' then null else substring(@split2,@a,1) end, @a=@a-1
insert @ta select case when @min_name is null then stuff(@num,@i,@j-@i+1,@max_name) else stuff(@num,@i,@j-@i+1,@min_name) end , case when @max_name is null then stuff(@num,@i,@j-@i+1,@min_name) else stuff(@num,@i,@j-@i+1,@max_name) end , @r, case when @max_name is null then 1 when @min_name is null then 2 else 0 end end end else begin select @split1=reverse(substring(@split,2,len(@split)-2)), @split2=null, @a=len(@split1) while @a>0 begin select @min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end, @max_name=null,@a=@a-1 insert @ta select case when @min_name is null then stuff(@num,@i,@j-@i+1,@max_name) else stuff(@num,@i,@j-@i+1,@min_name) end , case when @max_name is null then stuff(@num,@i,@j-@i+1,@min_name) else stuff(@num,@i,@j-@i+1,@max_name) end , @r, case when @max_name is null then 1 when @min_name is null then 2 else 0 end end
end end else begin if charindex('-',@num) between @i and @j begin select @split1=reverse(substring(@split,2,charindex('-',@split)-2)), @split2=reverse(substring(@split,charindex('-',@split)+1,len(@split)-charindex('-',@split)-1)), @a=case when len(@split1)>len(@split2) then len(@split1) else len(@split2) end while @a>0 begin select @min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end, @max_name=case when substring(@split2,@a,1)='' then null else substring(@split2,@a,1) end, @a=@a-1
insert @ta select case when @min_name is null then stuff(min_name,@i,@j-@i+1,@max_name) else stuff(min_name,@i,@j-@i+1,@min_name) end , case when @max_name is null then stuff(max_name,@i,@j-@i+1,@min_name) else stuff(max_name,@i,@j-@i+1,@max_name) end , @r,tag from @ta where level=@r-1 end end else begin select @split1=reverse(substring(@split,2,len(@split)-2)), @split2=null, @a=len(@split1) while @a>0 begin select @min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end, @max_name=null,@a=@a-1
insert @ta select case when @min_name is null then stuff(min_name,@i,@j-@i+1,@max_name) else stuff(min_name,@i,@j-@i+1,@min_name) end , case when @max_name is null then stuff(max_name,@i,@j-@i+1,@min_name) else stuff(max_name,@i,@j-@i+1,@max_name) end , @r,tag from @ta where level=@r-1 end
end endset @num=stuff(@num,@i,@j-@i+1,0)end select @s= isnull(@s+' ','')+a.min_name+ isnull('-'+case when min(b.max_name)=a.min_name then null else min(b.max_name) end,'')from @ta a join ( select * from @ta a where not exists (select 1 from @ta where level=a.level and tag<a.tag) )b on a.level=b.level and b.max_name>=a.min_name where not exists(select 1 from @ta where level>a.level ) group by a.min_name return @sendgo select id,num=dbo.test_f(num) from ta /* id num ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 87000-87099 2 87140-87149 87170-87179 87180-87189 3 890400 890700 890800 4 890900 5 8700-8799 8800-8899 8900-8999 6 8100-8999 8400-8999(6 行受影响) */--drop function test_f --drop table ta
楼主想要的效果作何效果改一下就行了,以下的tag只是判断的第一个[]的改变情况,可以新增两种,判断第二、三个[]。。主要是看显示的效果(情况比较多): declare @ta table (id int identity(1,1),min_name varchar(20),max_name varchar(20),level int,tag int)--返回值表变量case when @max_name is null then 1 when @min_name is null then 2 else --为判断
针对楼主有两个[]时的列子需要用函数拆分:create table ta(id int,num varchar(20))
insert ta select 1, '870[0-9][0-9]'
insert ta select 2, '871[478][0-9]'
insert ta select 3, '890[478]00'
insert ta select 4, '890900'
gocreate function test_f(@num varchar(50) )
returns varchar(1000)
as
begin
declare @i int,@j int,@r int,@split varchar(20),@s varchar(1000),@a int,@b int,@name varchar(20),@name1 varchar(20)
declare @tb table(id int)--辅助表变量
insert @tb select 0 union all select 1
union all select 2 union all select 3
union all select 4 union all select 5
union all select 6 union all select 7
union all select 8 union all select 9declare @ta table (con int identity(1,1),name varchar(1000))--返回值表变量
set @r=0
select @a=0,@b=0,@name=@num,@name1=@num
while charindex('-',@name)>0
select @name=stuff(@name,charindex('-',@name),1,''),@a=@a+1
while charindex('[',@name1)>0
select @name1=stuff(@name1,charindex('[',@name1),1,''),@b=@b+1while charindex('[',@num)>0
begin
set @r=@r+1
select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)
if @r=1
begin
if charindex('-',@num) between @i and @j
insert @ta
select stuff(@num,@i,@j-@i+1,substring(@num,@i+1,1))
union all
select stuff(@num,@i,@j-@i+1,substring(@num,@j-1,1)) else
insert @ta
select stuff(@num,@i,@j-@i+1,id) from @tb where id like @split
end
else
begin
if charindex('-',@num) between @i and @j
insert @ta select stuff(name,@i,@j-@i+1,min(id)) from @tb,@ta where id like @split group by name
union all select stuff(name,@i,@j-@i+1,max(id)) from @tb,@ta where id like @split group by name
else
insert @ta
select stuff(name,@i,@j-@i+1,id) from @tb,@ta where id like @split
end
set @num=stuff(@num,@i,@j-@i+1,0)
end
if @a=2
select @s=min(name)+'-'+max(name) from @ta where name not like '%[[]%'
else if @a+@b=1
select @s=isnull(@s+' ','')+name from @ta where name not like '%[[]%'
else if @a+@b=0
set @s=@num
else
select @s=isnull(@s+' ','')+min(b.name)+'-'+max(b.name)
from
(select * from @ta where name like '%[[]%')a
join
(select * from @ta where name not like '%[[]%')b
on b.name like a.name
group by a.namereturn @sendgo
select id,num=dbo.test_f(num) from taid num
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 87000-87099
2 87140-87149 87170-87179 87180-87189
3 890400 890700 890800
4 890900(所影响的行数为 4 行)--drop function test_f
--drop table ta
利用patindex('870[0-9][0-9]',87000)>0
create一個表變量, 像2個[]只要insert 87000-87099,然後利用patindex()可以將符合的紀錄都找出來
两个的[]可以实现了.
还有一种一个[]的情况:如8701[0-9] 能否也能通过上面的函数来实现?
如果有三个如:8[789][0-9][0-9]
8[14-9][0-9][0-9]
想显示成8700-8799 8800-8899 8900-8999
8100-8199 8400-8999谢谢
如果有patindex()来算,有没有具体的算法啊.谢谢
playwarcraft:你好.
如果有patindex()来算,有没有具体的算法啊.谢谢
同样需要表辅助
楼主参照一下~~
create table ta(id int,num varchar(20))
insert ta select 1, '870[0-9][0-9]'
insert ta select 2, '871[478][0-9]'
insert ta select 3, '890[478]00'
insert ta select 4, '890900'
insert ta select 5, '8[789][0-9][0-9]'
insert ta select 6, '8[14-9][0-9][0-9]'
go
alter function test_f(@num varchar(50))
returns varchar(1000)
as
begin
declare @i int,@j int,@r int,@split varchar(20),@s varchar(1000),@a int,@b int,
@c int,@name varchar(20),@name1 varchar(20),@name2 varchar(20)
declare @ta table (con int identity(1,1),name varchar(1000),level varchar(1000))--返回值表变量
select @a=0,@b=0,@c=0,@name=@num,@name1=@num,@name2=@num
while charindex('-',@name)>0
select @name=stuff(@name,charindex('-',@name),1,''),@a=@a+1
while charindex('[',@name1)>0
select @name1=stuff(@name1,charindex('[',@name1),1,''),@b=@b+1if charindex('[',@num)!>0
return @numwhile charindex('[',@num)>0
begin
set @c=@c+1
select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)while patindex('%[^0-9]%',@split)>0
set @split=stuff(@split,patindex('%[^0-9]%',@split),1,'')set @r=len(@split)if @c=1
while @r>0 and @c=1
begin
insert @ta(name) select stuff(@name2,@i,@j-@i+1,substring(@split,@r,1))
set @r=@r-1
end
else if @c=2
begin
while @r>0
begin
insert @ta
select stuff(name,@i,@j-@i+1,substring(@split,@r,1)),level=name
from @ta
set @r=@r-1
end
end
else
insert @ta
select stuff(name,@i,@j-@i+1,substring(@split,@r,1)),name from @ta
select @num=stuff(@num,@i,@j-@i+1,0)end
declare @tb table(id int identity(1,1),name varchar(20),level varchar(20))
insert @tb
select name,isnull(min(level),null)
from @ta a
where con>(select isnull(max(con),0) from @ta
where patindex('%[[]%',name)>0)
group by name order by nameif (select max(id)%2 from @tb)=0
select @s=isnull(@s+' ','')+
name+'-'+(select top 1 name from @tb where id%2=0 and id>a.id order by id)
from @tb a
where id%2=1
else
select @s=isnull(@s+' ','')+name
from @tb return @s
end
go
select id,num=dbo.test_f(num) from ta
id num
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 87000-87009 87090-87099
2 87140-87149 87170-87179 87180-87189
3 890400 890700 890800
4 890900
5 8709-8799 8809-8899 8909-8999
6 8109-8199 8409-8499 8909-8999(6 行受影响)
这个有点问题啊.
1, '870[0-9][0-9]'
2, '871[478][0-9]'
3, '890[478]00'
4, '890900'
5, '8[789][0-9][0-9]'
6, '8[14-9][0-9][0-9]'
结果应该显示成:
1, 87000-87099
2, 87140-87149 87170-87179 87180-87189
3, 890400 890700 890800
4, 890900
5, 8700-8799 8800-8899 8900-8999
6, 8100-8199 8400-8999
最后一个好象是把原有的顺利打乱了是吧.
这里写了一个思路,楼主看看问题有没有解决。
create table ta(id int,num varchar(20))
insert ta select 1, '870[0-9][0-9]'
insert ta select 2, '871[478][0-9]'
insert ta select 3, '890[478]00'
insert ta select 4, '890900'
insert ta select 5, '8[789][0-9][0-9]'
insert ta select 6, '8[14-9][0-9][0-9]'
go
create function test_f(@num varchar(50))
returns varchar(1000)
as
begin
declare @i int,@j int,@r int,@a int,@b int,
@split varchar(20),@split1 varchar(20),@split2 varchar(20),
@name varchar(20),@min_name varchar(20),@max_name varchar(20),@s varchar(1000)
declare @ta table (id int identity(1,1),min_name varchar(20),max_name varchar(20),level int,tag int)--返回值表变量select @r=0,@name=@numselect @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)
if charindex('[',@num)=0
return @num
while charindex('[',@num)>0
begin
set @r=@r+1
select @i=charindex('[',@num),@j=charindex(']',@num),@split=substring(@num,@i,@j-@i+1)if @r=1
begin
if charindex('-',@num) between @i and @j
begin
select @split1=reverse(substring(@split,2,charindex('-',@split)-2)),
@split2=reverse(substring(@split,charindex('-',@split)+1,len(@split)-charindex('-',@split)-1)),
@a=case when len(@split1)>len(@split2) then len(@split1) else len(@split2) end
while @a>0
begin
select
@min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end,
@max_name=case when substring(@split2,@a,1)='' then null else substring(@split2,@a,1) end,
@a=@a-1
insert @ta
select case when @min_name is null then stuff(@num,@i,@j-@i+1,@max_name) else
stuff(@num,@i,@j-@i+1,@min_name) end ,
case when @max_name is null then stuff(@num,@i,@j-@i+1,@min_name) else
stuff(@num,@i,@j-@i+1,@max_name) end ,
@r,
case when @max_name is null then 1 when @min_name is null then 2 else 0 end
end end
else
begin
select @split1=reverse(substring(@split,2,len(@split)-2)),
@split2=null,
@a=len(@split1)
while @a>0
begin
select
@min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end,
@max_name=null,@a=@a-1
insert @ta
select case when @min_name is null then stuff(@num,@i,@j-@i+1,@max_name) else
stuff(@num,@i,@j-@i+1,@min_name) end ,
case when @max_name is null then stuff(@num,@i,@j-@i+1,@min_name) else
stuff(@num,@i,@j-@i+1,@max_name) end ,
@r,
case when @max_name is null then 1 when @min_name is null then 2 else 0 end
end
end
end
else
begin
if charindex('-',@num) between @i and @j
begin
select @split1=reverse(substring(@split,2,charindex('-',@split)-2)),
@split2=reverse(substring(@split,charindex('-',@split)+1,len(@split)-charindex('-',@split)-1)),
@a=case when len(@split1)>len(@split2) then len(@split1) else len(@split2) end
while @a>0
begin
select
@min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end,
@max_name=case when substring(@split2,@a,1)='' then null else substring(@split2,@a,1) end,
@a=@a-1
insert @ta
select case when @min_name is null then stuff(min_name,@i,@j-@i+1,@max_name) else
stuff(min_name,@i,@j-@i+1,@min_name) end ,
case when @max_name is null then stuff(max_name,@i,@j-@i+1,@min_name) else
stuff(max_name,@i,@j-@i+1,@max_name) end ,
@r,tag
from @ta where level=@r-1
end end
else
begin
select @split1=reverse(substring(@split,2,len(@split)-2)),
@split2=null,
@a=len(@split1)
while @a>0
begin
select
@min_name=case when substring(@split1,@a,1)='' then null else substring(@split1,@a,1) end,
@max_name=null,@a=@a-1
insert @ta
select case when @min_name is null then stuff(min_name,@i,@j-@i+1,@max_name) else
stuff(min_name,@i,@j-@i+1,@min_name) end ,
case when @max_name is null then stuff(max_name,@i,@j-@i+1,@min_name) else
stuff(max_name,@i,@j-@i+1,@max_name) end ,
@r,tag
from @ta where level=@r-1
end
end endset @num=stuff(@num,@i,@j-@i+1,0)end
select @s=
isnull(@s+' ','')+a.min_name+
isnull('-'+case when min(b.max_name)=a.min_name
then null else min(b.max_name)
end,'')from
@ta a
join
(
select * from @ta a
where not exists
(select 1 from @ta where level=a.level and tag<a.tag)
)b
on a.level=b.level and b.max_name>=a.min_name
where
not exists(select 1 from @ta where level>a.level )
group by a.min_name
return @sendgo
select id,num=dbo.test_f(num) from ta
/*
id num
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 87000-87099
2 87140-87149 87170-87179 87180-87189
3 890400 890700 890800
4 890900
5 8700-8799 8800-8899 8900-8999
6 8100-8999 8400-8999(6 行受影响)
*/--drop function test_f
--drop table ta
declare @ta table (id int identity(1,1),min_name varchar(20),max_name varchar(20),level int,tag int)--返回值表变量case when @max_name is null then 1 when @min_name is null then 2 else --为判断