-1

I'm trying to pass a comma separated string such as this: "101:string1,102:string2" into a stored proc and create a temp table out of it. The temp table would have two columns, one integer and one string. It would have two rows for this example. The comma delimits the rows, and the colons delimit the two columns. Anyone know how I can do this? I'm using sql server. Thanks in advance!

EDIT: By the way, I'm not asking about how to create a temp table, only how to create the function.

gooseman
  • 425
  • 1
  • 6
  • 16
  • Are you sure a function is what you want? You can't create a #temp table inside a function. – Aaron Bertrand Oct 07 '14 at 17:26
  • Is your question primarily about creation of a table or about splitting the string? If the latter, see if this question helps: http://stackoverflow.com/questions/9589282/converting-comma-delimited-string-to-multiple-columns-in-sql-server – Andriy M Oct 07 '14 at 17:37

1 Answers1

5

You can try a Table-Valued Function instead of a temp table. Something like this:

CREATE FUNCTION ListToTable
(   
    @list nvarchar(4000)
)
RETURNS @return TABLE 
(
   n int,
   s nvarchar(15)
)
AS
BEGIN
   SET @list = NULLIF(ltrim(rtrim(@list)),'')

   DECLARE @xml AS XML = CAST('<root><row><n>' + 
                              REPLACE(REPLACE(@list,
                                    ',', '</s></row><row><n>'),
                                    ':', '</n><s>') + 
                              '</s></row></root>' AS XML) ;

   INSERT INTO @return (n, s)
      SELECT root.row.value('n[1]', 'int')
           , root.row.value('s[1]', 'nvarchar(4000)')
        FROM @xml.nodes('/root/row') as root(row)

   RETURN
END

Usage:

select * from dbo.ListToTable('101:string1,102:string2')

Output:

n           s
----------- ---------------
101         string1
102         string2
Wagner DosAnjos
  • 6,304
  • 1
  • 15
  • 29
  • Is there a reason for such inconsistency: `n[1]` and `./s[1]`? Why not either prefix both with a `./` or avoid using the prefix in both cases? – Andriy M Oct 08 '14 at 06:06
  • I fixed the inconsistency. Thanks! I was trying out different things and the './' was a left over. – Wagner DosAnjos Oct 08 '14 at 14:33