SQLServer查询问题

[horizon0928]

数据库中有一列数据中存储的数据使用“，”分开的，怎么在查询中把数据取出来。
例如此列的一条记录中存储的数据是“001，002，003，004，……，00n”
现在我只想取得其中一个数据，例如“001”，查询语句该怎么写。
请高手指导。

[horizon0928]

谢谢各位，可是我想取第二，第三个数据怎么做呢？

[dawugui]

[Quote=引用 5 楼 oldleft_hxb 的回复:]
SQL code
declare @obj varchar(5)
set @obj = '123'
select substring(dm,CHARINDEX(@obj, dm),LEN(@obj)) as objStr from tb
[/Quote]

/*
功能：实现split功能的函数
*/

create function dbo.fn_split 
(
@inputstr varchar(8000), 
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as 

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return 
end
go

--调用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split 

[sdhdy]

select left(col,3) from tb

[dawugui]

其中的一个?

如果是001就好办.


select left(col , charindex(',',col) - 1) from tb where charindex(',',col) > 0

[lgxyz]

DECLARE @S VARCHAR(10)
SET @S='007'
IF EXISTS (SELECT 1 FROM  TB WHERE CHARINDEX(@S,CODE)>0)
SELECT @S
ELSE 
SELECT @s+' 代码在表里不存在'

[moonshineidolon]

declare @tb1 table (contect varchar(100))
insert into @tb1 select '001,002,003,004,005,006'


SELECT


SUBSTRING(A.contect,B.number,CHARINDEX(',',A.contect+',',B.number)-B.number) AS contect
FROM @tb1 AS A
JOIN master..spt_values AS B
ON B.type='p' AND B.number >0
AND SUBSTRING(','+A.contect,B.number,1)=','

where SUBSTRING(A.contect,B.number,CHARINDEX(',',A.contect+',',B.number)-B.number) = '001'


(1 行受影响)
contect
----------------------------------------------------------------------------------------------------
001

(1 行受影响)

[oldleft_hxb]

declare @obj varchar(5)
set @obj = '123'
select substring(dm,CHARINDEX(@obj, dm),LEN(@obj)) as objStr from tb