如何拆分字段中1|2|3|4这样的字符串变成多个字段值
请问各位高人:
在mssql2000中,一个表中有org_path字段,这个字段中的数据类似这样:
org_path
---------------------------
1
1|2
1|28
1|31|124
1|31|124|192
1|5|134|156|168
最小值是 "1 ",最大的值如 1|5|134|156|168 ,包含5个值,4个|
现在我是想把org_path这字段中的值拆分成5个字段,如下:
org_path a b c d e
------------------------
1 1
1|2 1 2
1|28 1 28
1|31|124 1 31 124
1|31|6|192 1 31 6 192
1|5|134|156|168 1 5 134 156 168
请问,如果实现如上所需要的,应该怎么写SQL,请各位指导,多谢!
[解决办法]
declare @t table(org_path varchar(20))
insert into @t select '1 '
insert into @t select '1|2 '
insert into @t select '1|28 '
insert into @t select '1|31|124 '
insert into @t select '1|31|124|192 '
insert into @t select '1|5|134|156|168 '
select
a,
reverse(parsename(b,1)) as b,
reverse(parsename(b,2)) as c,
reverse(parsename(b,3)) as d,
reverse(parsename(b,4)) as e
from
(select
a,
reverse(replace(case when b= ' ' then b else left(b,len(b)-1) end , '| ' , '. ')) as b
from
(select
left(org_path,charindex( '| ',org_path+ '| ')-1) as a,
stuff(org_path+ '| ',1,charindex( '| ',org_path+ '| '), ' ') as b
from
@t
) s
)t
/*
a b c d e
---- ---- ---- ---- ----
1 NULL NULL NULL NULL
1 2 NULL NULL NULL
1 28 NULL NULL NULL
1 31 124 NULL NULL
1 31 124 192 NULL
1 5 134 156 168
*/
[解决办法]
学习,如果第一级是1
select 1,reverse(PARSENAME(A,1)),reverse(PARSENAME(A,2)),reverse(PARSENAME(A,3)),reverse(PARSENAME(A,4)) from
(select replace(reverse(stuff(org_path,1,2, ' ')), '| ', '. ')A from #a)aa
-----------------
1NULLNULLNULLNULL
12NULLNULLNULL
128NULLNULLNULL
131124NULLNULL
131124192NULL
15134156168
[解决办法]
create table tb(org_path varchar(32), a varchar(16),b varchar(16),c varchar(16),d varchar(16),e varchar(16))
go
insert tb(org_path) select '1 '
union all select '1|2 '
union all select '1|28 '
union all select '1|31|124 '
union all select '1|31|6|192 '
union all select '1|5|134|156|168 '
go
create function fnSplit(@FullString varchar(8000), @SplitChar char(1), @n int)
returns varchar(32) as
begin
set @FullString = @FullString + replicate(@SplitChar,@n)
declare @String varchar(512)
set @String = left(@FullString, charindex(@SplitChar,@FullString)-1)
while @n> 1
select @FullString=stuff(@FullString,1,len(@String)+1, ' '),
@n=@n-1,
@string=left(@FullString, charindex(@SplitChar,@FullString)-1)
return @String
end
go
update tb
set a=dbo.fnSplit(org_path, '| ',1),
b=dbo.fnSplit(org_path, '| ',2),
c=dbo.fnSplit(org_path, '| ',3),
d=dbo.fnSplit(org_path, '| ',4),
e=dbo.fnSplit(org_path, '| ',5)
select * from tb
go
drop function dbo.fnSplit
drop table tb
/**************
org_path a b c d e
-------------------------------- ---------------- ---------------- ---------------- ---------------- ----------------
1 1
1|2 1 2
1|28 1 28
1|31|124 1 31 124
1|31|6|192 1 31 6 192
1|5|134|156|168 1 5 134 156 168
(6 row(s) affected)
******************/
[解决办法]
--和这个差不多啊.
a1.1,1.2,1.3,1.4,1.5
b2.1,2.2,2.3
d3.1,3.2
e4.1,4.2
如何把以上的数据转为(1.1,1.2,1.3,1.4,1.5 是规定的5列)
row row1 row2 row3 row4 row5
----------------------------------------------
a 1.1 1.2 1.3 1.4 1.5
b 2.1 2.2 2.3 0 0
c 3.1 3.2 0 0 0
d 4.1 4.2 0 0 0
declare @t table(id varchar(4),code varchar(20))
insert into @t select 'a ', '1.1,1.2,1.3,1.4,1.5 '
insert into @t select 'b ', '2.1,2.2,2.3 '
insert into @t select 'd ', '3.1,3.2 '
insert into @t select 'e ', '4.1,4.2 '
declare @t table(id varchar(4),code varchar(20))
insert into @t select 'a ', '1.1,1.2,1.3,1.4,1.5 '
insert into @t select 'b ', '2.1,2.2,2.3 '
insert into @t select 'd ', '3.1,3.2 '
insert into @t select 'e ', '4.1,4.2 '
select
id,
row1,
isnull(REPLACE(reverse(PARSENAME(code2,1)), '; ', '. '),0) AS row2,
isnull(REPLACE(reverse(PARSENAME(code2,2)), '; ', '. '),0) AS row3,
isnull(REPLACE(reverse(PARSENAME(code2,3)), '; ', '. '),0) AS row4,
isnull(REPLACE(reverse(PARSENAME(code2,4)), '; ', '. '),0) AS row5
from
(select
id,
left(code,charindex( ', ',code)-1) as row1,
reverse(replace(replace(stuff(code,1,charindex( ', ',code), ' '), '. ', '; '), ', ', '. ')) as code2
from @t) a
/*
id row1 row2 row3 row4 row5
---- -------- -------- -------- -------- --------
a 1.1 1.2 1.3 1.4 1.5
b 2.1 2.2 2.3 0 0
d 3.1 3.2 0 0 0
e 4.1 4.2 0 0 0
*/
