Geeks With Blogs
Sharvan Dhaka blog
There is no in bulit function to split values in sql. i have created a tabular function that can split a string in a table.

Create function [dbo].[StrFunc_Split]
(
@InputStr nvarchar(4000),
@SplitChar nchar
)
returns @Splittings table
(
Position int,
Value nvarchar(4000)
)
as
begin
declare @Index int,
@LastIndex int,
@SNo int
set @LastIndex = 0
set @Index = charindex(@SplitChar, @InputStr)
set @SNo=0
while @Index > 0
begin
set @SNo=@SNo+1
insert into @Splittings(Position, Value)
values(@SNo, substring(@InputStr, @LastIndex, @Index - @LastIndex))
set @LastIndex = @Index +1
set @Index = charindex(@SplitChar, @InputStr, @LastIndex)
end
set @SNo=@SNo+1
insert into @Splittings(Position, Value)
values(@SNo, substring(@InputStr, @LastIndex, len(@InputStr) - @LastIndex + 1))
return
end
------------------------------------------------
to use this function

select * from StrFunc_Split('asdsad,asdsa,ff,fe3,5d,',',')
-------------------------------------------------


create FUNCTION [dbo].[getM]
(@idss varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
declare @cnt int
--select @return = 'ff'
select @cnt=count(*) from(select id from tbl_ where id in(select value from StrFunc_Split(@idss,','))) as cnt
declare @i int
declare @ids varchar(1000)
set @ids='0'
declare @id varchar(10)
set @i=0
declare @return varchar(8000)
set @return=''
declare @mSch varchar(1000)
while @i<@cnt begin select top 1 @mSch=id,@id=id from ( select top 200 nam,id from tbl_ where id in(select value from StrFunc_Split(@idss,',')) and id not in(select value from StrFunc_Split(@ids,',')) order by id ) as tbl order by id desc if len(@return) = 0 set @return = isnull(@mSch,'') else set @return=@return+'
'+isnull(@mSch,'')
set @ids=@ids+','+@id
set @i=@i+1
end
return @return
end
Posted on Sunday, October 15, 2006 5:39 PM MS Sql | Back to top


Comments on this post: Split function in MS Sql, Split string

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Sharvan Dhaka | Powered by: GeeksWithBlogs.net