FUNCTION [dbo].[SplitString]
(  @Delimiter varchar(1), 
      @List      varchar(8000)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         --[Value] varchar(50) 
         [Value] int
      )

 
AS
BEGIN
      DECLARE @LenString int 
 
      WHILE len( @List ) > 0 
         BEGIN 
         
            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 
                                
            INSERT INTO @TableOfValues 
               SELECT SUBSTRING(@List, 1, @LenString)
                
            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END
          
      RETURN 
END

quote:

---

Originally posted by visakh16

I didnt understand why you're following such a design for sending data. instead of using delimited strings with all kinds of delimiters it would be much better if you had passed values as a xml with node names representing what data they contain. that would improve clarity and would allows others also to understand what data it contains and where it should get populated.By making it XML, you can also make use of new xml methods available from 2005 onwards like nodes(),query() etc to shred the component on to different fields of table which would perform much better than parsing logic using udf. You even have ability to add XML indexes if you want to further speed up the search...

---