表的内容如下:
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多谢!
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多谢!
http://topic.csdn.net/u/20091108/15/b2bf70f3-2459-4c85-a8aa-10a665af4750.html?25689
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