求解一条SQL语句？

Question

一个表的其中一个字段是text    其值类似于1850,1853,10
通过sql语句如何分别找到1850和1853和10

Answer 1

create table tb(F1 varchar(10),F2 varchar(20))
insert into tb select 'a','1,2,5'
insert into tb select 'b','1,3'
insert into tb select 'c','3,5'
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.F1, SUBSTRING(A.F2, B.id, CHARINDEX(',', A.F2 + ',', B.id) - B.id) F2
FROM tb A, # B
WHERE SUBSTRING(',' + A.F2, B.id, 1) = ','
/*
F1         F2
---------- --------------------
a          1
a          2
a          5
b          1
b          3
c          3
c          5(7 行受影响)*/
go
DROP TABLE tb,#

Answer 2

select substring(f2,1,1) as f2 from tb where charindex('2',f2)>0 or charindex('3',f2)>0 or  charindex('5',f2)>0 

Answer 3

select * from tb
 select F1,SUBSTRING(F2,1,CHARINDEX(',',F2)-1)as 第一列 ,
substring(RIGHT(F2,len(F2)-CHARINDEX(',',F2)),1,CHARINDEX(',',F2)-1)as  第二列
 from tb
 
F1         F2
---------- --------------------
a          1,2,5
b          1,3
c          3,5
c          3,5,433
c          3,24(5 行受影响)F1         第一列                  第二列
---------- -------------------- --------------------
a          1                    2
b          1                    3
c          3                    5
c          3                    5
c          3                    2(5 行受影响)

Answer 4

貌似不太对,如何第一个不是单位数的话，就错了,修改如下:
select F1,SUBSTRING(F2,1,CHARINDEX(',',F2)-1) as 第一列,
substring(RIGHT(F2,LEN(F2)-CHARINDEX(',',F2)),1,CHARINDEX(',',RIGHT(F2,LEN(F2)-CHARINDEX(',',F2)))-1) as 第二列
FROM tb

Answer 5

不好意思，没考虑到， 你的也写错了，经过测试 正确的如下create table tb(F1 varchar(10),F2 varchar(20))
insert into tb select 'a','1,2,5'
insert into tb select 'b','1,33'
insert into tb select 'c','333,5'
insert into tb select 'c','3,5,433'
insert into tb select 'c','3,23424'
go drop table tb
 
 
 select F1,F2,SUBSTRING(F2,1,CHARINDEX(',',F2)-1) as 第一列,
case when charindex(',',RIGHT(F2,len(F2)-CHARINDEX(',',F2)))>0 then
 SUBSTRING(RIGHT(F2,len(F2)-CHARINDEX(',',F2)),1,CHARINDEX(',',RIGHT(F2,len(F2)-CHARINDEX(',',F2)))-1)
 else RIGHT(F2,len(F2)-CHARINDEX(',',F2)) end as 第二列
FROM tbF1         F2                   第一列                  第二列
---------- -------------------- -------------------- --------------------
a          1,2,5                1                    2
b          1,33                 1                    33
c          333,5                333                  5
c          3,5,433              3                    5
c          3,23424              3                    23424(5 行受影响)

Answer 6

 select F1,F2,SUBSTRING(F2,1,CHARINDEX(',',F2)-1) as 第一列,
case when charindex(',',RIGHT(F2,len(F2)-CHARINDEX(',',F2)))>0 then
 SUBSTRING(RIGHT(F2,len(F2)-CHARINDEX(',',F2)),1,CHARINDEX(',',RIGHT(F2,len(F2)-CHARINDEX(',',F2)))-1)
 else RIGHT(F2,len(F2)-CHARINDEX(',',F2)) end as 第二列
FROM tb
F1         F2                   第一列                  第二列
---------- -------------------- -------------------- --------------------
a          1,2,5                1                    2
b          1,33                 1                    33
c          333,5                333                  5
c          3,5,433              3                    5
c          3,23424              3                    23424(5 行受影响)