请教:如何根据 [] 来分解字符串?
有这样的数据如:
1 870[0-9][0-9]
2 871[478][0-9]
3 890[478]00
想把它拆分成这样的数据
1 87000-87099
2 87140-87149 88770-87179 87180-87189
3 890400 890700 890800
请问用sql语句怎么样实现啊.
谢谢!
[解决办法]
如果有三个[]时，分别怎样显示?
针对楼主有两个[]时的列子需要用函数拆分:
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 '
go
create 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 9
declare @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+1
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
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.name
return @s
end
go
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
（所影响的行数为 4 行）
--drop function test_f
--drop table ta
[解决办法]
也可以用patindex,
利用patindex( '870[0-9][0-9] ',87000)> 0
create一個表變量, 像2個[]只要insert 87000-87099,然後利用patindex()可以將符合的紀錄都找出來
