急,急,关于一个字段查询的问题

[mysohu]

问题是这样的,有一个字段存的是一个字符串
表temp
id words
1 'a,b,c,d'
2 'g,h,d,w'
3 'i,e,t,r'


查询的时候,给一个字符a,查询到它所在的words的id

select id from temp where words ? ='a'

应该怎么写呀,请高手指教,谢谢!

[playwarcraft]

where charindex(',a,',','+words+',')>0

[自由的企鹅]

select id from temp where substring(words,1,1) ='a',其他的可以加循環處理

[playwarcraft]

create table [temp] ( id int,words varchar(10))
insert into [temp] select 1,'a,b,c,d'
insert into [temp] select 2,'g,h,d,w'
insert into [temp] select 3,'i,e,t,r'
insert into [temp] select 4,'k,j,a,f'
insert into [temp] select 5,'s,x,y,a'

select id from [temp] where charindex(',a,',','+words+',')>0
--drop table [temp]


/*result:*/
id
-----------
1
4
5

[mysohu]

谢谢以上各位,马上结贴

[mysohu]

楼上的,如果改成字符串,怎么处理呀
create table [temp] ( id int,words varchar(10))
insert into [temp] select 1,'12,23,14,89'
insert into [temp] select 2,'23,45,67,29'
insert into [temp] select 3,'33,45,67,99'
insert into [temp] select 4,'43,56,45,29'
insert into [temp] select 5,'63,45,76,29'

select id from [temp] where charindex(',23,',','+words+',')>0
查不出来呀

[playwarcraft]

create table [temp] ( id int,words varchar(10))
---改varchar(50)看看

[amker]

为什么这样不行？

select id from temp where words like '%a%'

[砸死牛顿的苹果]

select id from [temp] where charindex(',23,',','+words+',')>0
改为
select id from [temp] where charindex('23',','+words+',')>0
即可
你不能保证'23' 在中间 所以前面不能有','

[砸死牛顿的苹果]

你们上面说的2种方法我这里都可以找到
mysohu 很奇怪你为什么不行

[playwarcraft]

樓上的2位,要是這樣的數據呢? 2323,234,232334

[砸死牛顿的苹果]

先前考虑不周到 ~.~
select id from [temp] where charindex(',23,',','+words+',')>0
这句我这没问题的

[mysohu]

谢谢以上各位