use Test go create table T(code int,value nvarchar(20)) insert T select 1,'01,02,03' insert T select 2,'04,05' goselect top 100 ID=identity(int,1,1) into # from sysobjects a ,syscolumns b--辅助表 goselect T.code,[value]=substring([value],ID,charindex(',',[value]+',',ID)-ID) from T cross join # T2 where
go
create table T(code int,value nvarchar(20))
insert T select 1,'01,02,03'
insert T select 2,'04,05'
goselect top 100 ID=identity(int,1,1) into # from sysobjects a ,syscolumns b--辅助表
goselect
T.code,[value]=substring([value],ID,charindex(',',[value]+',',ID)-ID)
from
T
cross join
# T2
where
ID<=(len(value+','))and
charindex(',',','+value,ID)=IDcode value
----------- --------------------
1 01
1 02
1 03
2 04
2 05(所影响的行数为 5 行)
select * from tb where charindex( ',02',''''+value,1) >0
go
用以上生成函数表时:
select * from t where exists(select 1 from 函数名 where value='02' and code=t.code)