分段截取字符串

[棉花棒棒糖]

aa/bbb/ccc
bb/cccc/ddd/eee/ff/gg
11/22/33/44/21
....

结果
aa
bbb
ccc


bb
cccc
ddd
eee
ff
gg

11
22
33
44
21

[棉花棒棒糖]

with T1(C) as (select 第一列 from tb1) select SUBSTRING(C,number,charindex('/',C+'/',number)-number) from t1, master..spt_values where type='P' and number>=1 and number<=LEN(C) and SUBSTRING('/'+C,number,1)='/' 其中对应的第一列值为911|6666|765765|ew,执行结果也是911|6666|765765|ew 没有截取啊..

[棉花棒棒糖]

明白了,是分隔符不正确.

[棉花棒棒糖]

with T1(C) as (select 第一列 from tb1) select SUBSTRING(C,number,charindex('|',C+'|',number)-number) from t1, master..spt_values where type='P' and number>=1 and number<=LEN(C) and SUBSTRING('|'+C,number,1)='| 在此基础上我再join一个表,怎么写啊?

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([Col1] nvarchar(21))
Insert #T
select N'aa/bbb/ccc' union all
select N'bb/cccc/ddd/eee/ff/gg' union all
select N'11/22/33/44/21'
Go
SELECT  [Col1],SUBSTRING([Col1],b.number,CHARINDEX('/',[Col1]+'/',b.number)-b.number) AS 分拆后数据
FROM    #T AS a
       ,master.dbo.spt_values AS b
WHERE   CHARINDEX('/', '/' + [Col1], b.number) = b.number
        AND b.type = 'P'
        AND b.number > 0
/*
Col1	分拆后数据
aa/bbb/ccc	aa
aa/bbb/ccc	bbb
aa/bbb/ccc	ccc
bb/cccc/ddd/eee/ff/gg	bb
bb/cccc/ddd/eee/ff/gg	cccc
bb/cccc/ddd/eee/ff/gg	ddd
bb/cccc/ddd/eee/ff/gg	eee
bb/cccc/ddd/eee/ff/gg	ff
bb/cccc/ddd/eee/ff/gg	gg
11/22/33/44/21	11
11/22/33/44/21	22
11/22/33/44/21	33
11/22/33/44/21	44
11/22/33/44/21	21
*/

[中国风]

分拆其它方法 http://bbs.csdn.net/topics/230087434

[xxfvba]

with T(C) as (select 'aa/bbb/ccc' union all select ' bb/cccc/ddd/eee/ff/gg' union all select '11/22/33/44/21') select SUBSTRING(C,number,charindex('/',C+'/',number)-number) from t,master..spt_values where type='P' and number>=1 and number<=LEN(C) and SUBSTRING('/'+C,number,1)='/'