字符串拆分,急!

Question

表的内容如下:
SN                    T1     T2   T3  T4 T5
A/I01/P01/01      
A/I01/P02/03
A/I023/01
A/I00
希望拆分的结果如下:
SN                    T1     T2   T3   T4 T5
A/I01/P01/01    A     I01   P01  01
A/I01/P02/03   A     I01   P02  03
A/I023/01        A     I023  01
A/I00                A    I00多谢!

Answer 1

/*
-----------------------------------
 -------T-MAC ---------------------
 ---------------小编---------------
   ---------------love 轩--------
-----------------------------------
*/
拆分表:
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:--此法roy博客;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 2

declare @s varchar(100),@sql varchar(1000)
set @s='A/IO1/Pos/0s'
set @sql='select col='''+ replace(@s,'/',''', ''')+''''
exec (@sql)

Answer 3

create table Test(SN nvarchar(20),T1 nvarchar(10),T2 nvarchar(10),T3 nvarchar(10),T4 nvarchar(10),T5 nvarchar(10))insert into Test(SN) select 
'A/I01/P01/01' union all select      
'A/I01/P02/03' union all select
'A/I023/01'    union all select
'A/I00'  select SN,
T1=Max(Case ID When 1 then T  else '' end),
T2=Max(Case ID When 2 then T  else '' end),
T3=Max(Case ID When 3 then T  else '' end),
T4=Max(Case ID When 4 then T else '' end)
From 
(
select Row_Number() over(partition by SN order by p.Number) ID,SN,T=SubString('/'+SN,P.Number+1,charindex('/','/'+Test.SN+'/',P.Number+1)-P.Number-1) 
from master..spt_values P
inner join Test 
On P.Type='P' and charindex('/','/'+Test.SN,P.Number)=P.Number
)  V
Group By SN
drop table test
/*
SN                   T1                    T2                    T3                    T4
-------------------- --------------------- --------------------- --------------------- ---------------------
A/I00                A                     I00                                         
A/I01/P01/01         A                     I01                   P01                   01
A/I01/P02/03         A                     I01                   P02                   03
A/I023/01            A                     I023                  01                    (4 行受影响)
*/

Answer 4

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-11-08 16:01:13
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (SN varchar(12),T1 varchar(12),T2 varchar(12),T3 varchar(12),T4 varchar(12),T5 varchar(12))
INSERT INTO @tb
SELECT 'A/I01/P01/01',null,null,null,null,null UNION ALL
SELECT 'A/I01/P02/03',null,null,null,null,null UNION ALL
SELECT 'A/I023/01',null,null,null,null,null UNION ALL
SELECT 'A/I00',null,null,null,null,null--SQL查询如下:UPDATE A SET
    T1=B.flag.value('(//v)[1]','varchar(12)'),
    T2=B.flag.value('(//v)[2]','varchar(12)'),
    T3=B.flag.value('(//v)[3]','varchar(12)'),
    T4=B.flag.value('(//v)[4]','varchar(12)'),
    T5=B.flag.value('(//v)[5]','varchar(12)')
FROM @tb AS A
    CROSS APPLY (
        SELECT CONVERT(xml,'<v>'+REPLACE(A.SN,'/','</v><v>')+'</v>') AS flag
    ) AS B;
    
SELECT * FROM @tb;/*
SN           T1           T2           T3           T4           T5
------------ ------------ ------------ ------------ ------------ ------------
A/I01/P01/01 A            I01          P01          01           NULL
A/I01/P02/03 A            I01          P02          03           NULL
A/I023/01    A            I023         01           NULL         NULL
A/I00        A            I00          NULL         NULL         NULL(4 行受影响)
*/

Answer 5

[code=SQL]
Select SN,[1] as T1,[2] as T2,[3] as T3,[4] as T4
from 
(
select Row_Number() over(partition by SN order by p.Number) ID,SN,T=SubString('/'+SN,P.Number+1,charindex('/','/'+Test.SN+'/',P.Number+1)-P.Number-1) 
from master..spt_values P
inner join Test 
On P.Type='P' and charindex('/','/'+Test.SN,P.Number)=P.Number
) v pivot (max(T) for id in ([1],[2],[3],[4])) AS pvt/*
SN                   1                     2                     3                     4
-------------------- --------------------- --------------------- --------------------- ---------------------
A/I00                A                     I00                   NULL                  NULL
A/I01/P01/01         A                     I01                   P01                   01
A/I01/P02/03         A                     I01                   P02                   03
A/I023/01            A                     I023                  01                    NULL(4 行受影响)*/
[code]顺序又不对 没办法排序

Answer 6

 
Select SN,[1] as T1,[2] as T2,[3] as T3,[4] as T4 
from 
( 
select Row_Number() over(partition by SN order by p.Number) ID,SN,T=SubString('/'+SN,P.Number+1,charindex('/','/'+Test.SN+'/',P.Number+1)-P.Number-1) 
from master..spt_values P 
inner join Test 
On P.Type='P' and charindex('/','/'+Test.SN,P.Number)=P.Number 
) v pivot (max(T) for id in ([1],[2],[3],[4])) AS pvt /* 
SN                  1                    2                    3                    4 
-------------------- --------------------- --------------------- --------------------- --------------------- 
A/I00                A                    I00                  NULL                  NULL 
A/I01/P01/01        A                    I01                  P01                  01 
A/I01/P02/03        A                    I01                  P02                  03 
A/I023/01            A                    I023                  01                    NULL (4 行受影响) */ 
 顺序又不对 没办法排序梁哥好厉害 

Answer 7

用的是SQL 2000,梁哥的办法用不了啊!

Answer 8

create table Test(SN nvarchar(20),T1 nvarchar(10),T2 nvarchar(10),T3 nvarchar(10),T4 nvarchar(10),T5 nvarchar(10))insert into Test(SN) select 
'A/I01/P01/01' union all select      
'A/I01/P02/03' union all select
'A/I023/01'    union all select
'A/I00'  
---------2000函数
if object_id('f_split') is not null
drop function f_split
go
CREATE function f_split(@SourceStr varchar(8000),@StrSeprate varchar(10),@n int)   
returns nvarchar(10)
as     
begin   
      declare @i int       
  declare @j int
  declare @ret nvarchar(10)
      set @SourceStr=rtrim(ltrim(@SourceStr))+ @StrSeprate
      set @i=1
  set @j=1      while @j<=@n
      begin 
set   @i=charindex(@StrSeprate,@SourceStr) 
if @n=@j 
return left(@SourceStr,@i-1)  

if len(@SourceStr)-@i < 1 
return ''
            set @SourceStr=substring(@SourceStr,@i+1,len(@SourceStr)-@i)   
set @j=@j+1
      end     
  return ''
end  
-------查询Select SN,
T1=dbo.f_split(SN,'/',1),
T2=dbo.f_split(SN,'/',2),
T3=dbo.f_split(SN,'/',3),
T4=dbo.f_split(SN,'/',4)
from Test
----------结果
/*SN                   T1         T2         T3         T4
-------------------- ---------- ---------- ---------- ----------
A/I01/P01/01         A          I01        P01        01
A/I01/P02/03         A          I01        P02        03
A/I023/01            A          I023       01         
A/I00                A          I00                   (4 行受影响)*/drop table test

Answer 9

create table tb(SN varchar(20))
insert into tb values('A/I01/P01/01')      
insert into tb values('A/I01/P02/03') 
insert into tb values('A/I023/01') 
insert into tb values('A/I00')
goselect t1 = parsename(replace(sn , '/','.'),4), 
       t2 = parsename(replace(sn , '/','.'),3), 
       t3 = parsename(replace(sn , '/','.'),2), 
       t4 = parsename(replace(sn , '/','.'),1)
from tb where parsename(replace(sn , '/','.'),4) is not null
union all
select t1 = parsename(replace(sn , '/','.'),3), 
       t2 = parsename(replace(sn , '/','.'),2), 
       t3 = parsename(replace(sn , '/','.'),1), 
       t4 = ''
from tb where parsename(replace(sn , '/','.'),3) is not null and parsename(replace(sn , '/','.'),4) is null
union all
select t1 = parsename(replace(sn , '/','.'),2), 
       t2 = parsename(replace(sn , '/','.'),1), 
       t3 = '', 
       t4 = ''
from tb where parsename(replace(sn , '/','.'),2) is not null and parsename(replace(sn , '/','.'),4) is null and parsename(replace(sn , '/','.'),3) is null
union all
select t1 = parsename(replace(sn , '/','.'),1), 
       t2 = '', 
       t3 = '', 
       t4 = ''
from tb where parsename(replace(sn , '/','.'),1) is not null and parsename(replace(sn , '/','.'),4) is null and parsename(replace(sn , '/','.'),3) is null and parsename(replace(sn , '/','.'),2) is nulldrop table tb /*
t1                                                                                                                               t2                                                                                                                               t3                                                                                                                               t4                                                                                                                               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- 
A                                                                                                                                I01                                                                                                                              P01                                                                                                                              01
A                                                                                                                                I01                                                                                                                              P02                                                                                                                              03
A                                                                                                                                I023                                                                                                                             01                                                                                                                               
A                                                                                                                                I00                                                                                                                                                                                                                                                               （所影响的行数为 4 行）*/