查找某个字段中的字符

/*
   --update dbo.newsDemo set [content]='1|3|1000|6'  where id=173
   功能:查找某个字段('1|3|1000|6')某个字符
   作者：merrick.xia
   日期:2010-4-24
   用法：dbo.splitStr(字段,'分隔符',位置),比如获得:1000,dbo.splitStr('1|3|1000|6',',',4)
   原理：'1|3|1000|6' ，其实都存入了@RtnValue表中1->1,2->3,3->1000,4->6,5->'1|3|1000|6'
*/

CREATE FUNCTION  splitStr
(
    @List nvarchar(2000),--要分隔的字符串
    @SplitOn nvarchar(5),--分隔符
    @num int --比如你要得到1000，请输入4(索引从1开始);
) 
RETURNS varchar(50)
as
BEGIN
    declare @temp varchar(100)
    declare @RtnValue table/*创建表格*/
    (
        Id int identity(1,1),
        [Value] nvarchar(100)
    )
    /*循环截取存入表格中*/
    While (Charindex(@SplitOn,@List)>0)
    Begin 
        Insert Into @RtnValue ([Value])
        Select 
            [Value]= ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
            Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End 
       Insert Into @RtnValue ([Value])
       Select [Value] = ltrim(rtrim(@List))
       select @temp=[Value] from @RtnValue where Id=@num-1
       return @temp
END
go
select * from dbo.newsDemo where dbo.splitStr([content],'|',3)='3'
drop function splitStr