1

I found this beautiful XML function to split comma separated string

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT * 
FROM
    SomeTable 
    INNER JOIN @XML.nodes('i') x(i) 
        ON  SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

Is it possible to split the values without joining to SomeTable?

e.g.

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

--SELECT @XML.nodes('i')
--which would need to return

1
2
3
5
4
6
7
98
234
Community
  • 1
  • 1
Peter PitLock
  • 1,823
  • 7
  • 34
  • 71
  • 1
    It would be a lot better if the starting point here wasn't a comma separated string. SQL Server has two data types *designed* for holding multiple values - tables and XML. If possible, switch to using one of those as your input. – Damien_The_Unbeliever Sep 15 '15 at 06:37
  • Agreed, this is to get rid of the old fn_Split, without creating new table valued parameters. – Peter PitLock Sep 15 '15 at 06:39

1 Answers1

1

That's perfectly possible, for example, assuming that you want to return IDs as integer values :

Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'

DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)

SELECT x.i.value('.', 'INT') as Id 
FROM @XML.nodes('i') x(i)
har07
  • 88,338
  • 12
  • 84
  • 137