This blog explains how to a convert a delimiter-separated list into a table. First of all we need to define a table valued function that takes two parameters; the first parameter is a list and the second parameter is a delimiter character and returns a table. Then we use this function to generate a table from list.
Function:

  1. CREATE FUNCTION dbo.Fun_ListTOTable
  3. (
  5. @String_List VARCHAR(MAX),
  7. @Delim_String CHAR
  9. )
  11. RETURNS
  13. @Tab TABLE
  15. (
  17. item VARCHAR(MAX)
  19. )
  21. AS
  23. BEGIN
  25. DECLARE @item VARCHAR(MAX), @Pos INT
  27. SET @String_List = @String_List+ @Delim_String;
  29. WHILE LEN(@String_List)>0
  31. BEGIN
  33. SET @Pos = CHARINDEX(@Delim_String, @String_List, 1)
  35. SET @item =SUBSTRING(@String_List,1,@Pos-1);
  37. IF LEN(LTRIM(@item))>0
  39. BEGIN
  41. INSERT INTO @Tab (item)
  43. VALUES (CAST(@item AS VARCHAR(MAX)))
  45. END
  47. SET @String_List = SUBSTRING(@String_List,@Pos+1,LEN(@String_List));
  49. END
  51. RETURN
  53. END
  55. GO

Example:

  1. SELECT item AS Data
  3. FROM dbo.Fun_ListTOTable('Pankaj#Sandeep#Neeraj#Sanjjev#Naru','#')

Result: