create table #str(char1 char(1))insert into #str
select 'a'
union
select 'b'
union
select 'c'
union
...
select 'z'
select distinct a.char1 from #str a,t b
where charindex(a.char1,b.code) > 1
order by a.char1drop table #str
select 'a'
union
select 'b'
union
select 'c'
union
...
select 'z'
select distinct a.char1 from #str a,t b
where charindex(a.char1,b.code) > 1
order by a.char1drop table #str
id code
1 1312122;1322122
2 1332111;1332112
3 1321211;1310921
结果
code
1310921
1312122
1321211
1332112
1322122
select distinct(substring(code,b.id,charindex(';',code,b.id+1)-b.id))
from 表 ,#t b
where substring(code,b.id,1)=';' and b.id<len(code)
向 substring 函数传递了无效的 length 参数。
select distinct(substring(code+';',b.id,charindex(';',code+';',b.id+1)-b.id))
from 表 ,#t b
where substring(code,b.id,1)=';' and b.id<len(code+';')
code
;1310921
;1312122
;1321211
select distinct(substring(code+';',b.id+1,charindex(';',code+';',b.id+1)-b.id))
from 表 ,#t b
where substring(code,b.id,1)=';' and b.id<len(code+';')
from 表 ,#t b
where substring(code,b.id,1)=';' and b.id<len(code+';')
PRIMARY KEY CLUSTERED,code char(100))
insert into sect(code)
values('1312122;1322122')
insert into sect(code)
values('1312178;1322766')
insert into sect(code)
values('1312165;1322172')
go
select id=identity(int,1,1) into #t from sysobjects a,sysobjects bselect substring(';'+rtrim(code)+';',b.id+1,charindex(';',';'+rtrim(code)+';',b.id+1)-b.id-1) as t
from sect ,#t b
where substring(';'+rtrim(code)+';',b.id,1)=';' and b.id<len(';'+rtrim(code)+';')
set rowcount @maxlen
select id=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0select substring(';'+rtrim(code)+';',
b.id+1,
charindex(';',';'+rtrim(code)+';',b.id+1)-b.id-1) as t
from sect ,#t b
where substring(';'+rtrim(code)+';',b.id,1)=';'
and b.id<len(';'+rtrim(code)+';')