update Survey_QuestionColumns set ColumnPath='1|3|1000|6' where ColumnPath='1|3|4|6'
CREATE FUNCTION Spliaaaa
(
@List nvarchar(2000),--要分隔的字符串
@SplitOn nvarchar(5),--分隔符
@num int
)
RETURNS varchar(50)
as
BEGIN
declare @aaa varchar(50)
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 @aaa=[Value] from @RtnValue where Id=@num-1
return @aaa
END
go
select * from Survey_QuestionColumns where dbo.Spliaaaa(ColumnPath,'|',3)='3'
drop function Spliaaaa