Hot to convert a variable with value '1,2,3' to a table (every number as a record)

Question

1. Working on SQL (2005 and 2008)

2. the variable with value '1,2,3' would be call @cedis and this could to have N number for example

   set @cedis='1' or set @cedis='1,2,3,4,5,6,7' or set @cedis='125,98,91'
   

3. so important, its this must to be a select only, a loop could not to be use, only a select!

4. this must to return a (result as ) table with values for example

   set @cedis='1,2,3,4' this must to return a result 
   number 1 2 3 4
   
   declare @cedis varchar(max)
   set @cedis='1,58,123,8'
   ;with datos as 
   (
      my select with is going to return me the table
   )
   select * from datos 
   

result set is

number
1
58
123
8

Answer 1

If am not wrong this is what you need

DECLARE @cedis VARCHAR(500)='1,2,3,4'

SELECT Split.a.value('.', 'VARCHAR(100)') Numbers
FROM   (SELECT Cast ('<M>' + Replace(@cedis, ',', '</M><M>') + '</M>' AS XML) AS Numbers) AS A
       CROSS APPLY Numbers.nodes ('/M') AS Split(a) 

Result:

Numbers
-------
1
2
3
4

Answer 2

A table valued function would do it.

CREATE FUNCTION [dbo].[fn_Split](@text VARCHAR(MAX), @delimiter VARCHAR(5) = ',')
RETURNS @Strings TABLE
(    
    position int IDENTITY PRIMARY KEY,
    value VARCHAR(8000)  
)
AS
BEGIN
    DECLARE @index int 
    SET @index = -1 
    WHILE (LEN(@text) > 0) 
        BEGIN  
            SET @index = CHARINDEX(@delimiter , @text)  
            IF (@index = 0) AND (LEN(@text) > 0)  
                BEGIN   
                    INSERT INTO @Strings VALUES (@text)
                    BREAK  
                END  
            IF (@index > 1)
                BEGIN
                    INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
                END
            SET @text = RIGHT(@text, (LEN(@text) - (@index+LEN(@delimiter)-1))) 
        END
    RETURN
END

You can call it as follows:

SELECT *
FROM dbo.fn_Split(@cedis,',')

Answer 3

Here is a more generic solution that breaks any given string into a table based on any given separator:

http://rextester.com/VSRDLS48817

Not an original idea, but I've found it very useful.

create function [dbo].[SplitString] 
(
    @str nvarchar(255), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as int), 
        cast(1 as int), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS Item
from tokens
);

Answer 4

This is another one approach to get required output result

DECLARE @cedis VARCHAR(MAX) ,
@delimeter VARCHAR(10)

SET @cedis = '1,58,123,8,14144,15,155231,15,3647,2347,45,76,68,2354,577,5'
SET @delimeter = ','
SET @cedis = @cedis + @delimeter;
WITH    datos
          AS ( SELECT   n = 1
               UNION ALL
               SELECT   n + 1
               FROM     datos
               WHERE    n <= LEN(@cedis)
             ),
        cte
          AS ( SELECT   T.N ,
                        ROW_NUMBER() OVER ( ORDER BY T.N ) AS RN
               FROM     datos AS T
               WHERE    SUBSTRING(@cedis, T.N, LEN(@delimeter)) = @delimeter
                        AND LEN(@cedis) >= T.N
             )
    SELECT  SUBSTRING(@cedis, COALESCE(R.N + LEN(@delimeter), 1),
                      L.N - COALESCE(R.N + LEN(@delimeter), 1)) AS part ,
            L.RN AS ID
    FROM    cte AS L
            LEFT JOIN cte AS R ON L.RN = R.RN + 1
OPTION  ( MAXRECURSION 1000 )