SQL statement that returns id and separates comma separated values

Question

I have a table with the following data:

  NodeId ExternalIds
 50 601
 56,700,701

How to write an SQL statement that breaks the ExternalIds column and returns:

  NodeId ExternalIds
 50 601
 56,700
 56,701

I have found many user-defined functions and procedures that break a row into a table, but I cannot get them to work

change

  create table #tmpTable (NodeId int, ExternalIds varchar (50))
 insert into #tmpTable (NodeId, ExternalIds) values ​​(50, '600')
 insert into #tmpTable (NodeId, ExternalIds) values ​​(56, '700,701')

 select NodeId, 
     (SELECT * FROM [dbo]. [SplitString] (select * from #tmpTable, ',') where NodeId = 56) from #tmpTable)
 where NodeId = 56
 drop table #tmpTable

where SplitString is based on the following:

  SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO

 Create FUNCTION [dbo]. [SplitString] 
 (
     - Add the parameters for the function here
     @myString varchar (500),
     @deliminator varchar (10)
 )
 Returns 
 @ReturnTable TABLE 
 (
     - Add the column definitions for the TABLE variable here
     [id] [int] IDENTITY (1,1) NOT NULL,
     [part] [varchar] (50) NULL
 )
 As
 BEGIN
         Declare @iSpaces int
         Declare @part varchar (50)

         --initialize spaces
         Select @iSpaces = charindex (@ deliminator, @ myString, 0)
         While @iSpaces> 0

         Begin
             Select @part = substring (@ myString, 0, charindex (@ deliminator, @ myString, 0))

             Insert Into @ReturnTable (part)
             Select @part

     Select @myString = substring (@ mystring, charindex (@ deliminator, @ myString, 0) + len (@deliminator), len (@myString) - charindex ('', @ myString, 0))


             Select @iSpaces = charindex (@ deliminator, @ myString, 0)
         end

         If len (@myString)> 0
             Insert Into @ReturnTable
             Select @myString

     Return 
 End

I am trying to get some data from a database for Umbraco (cms) which is for comma-separated values.

Thank you Thomas

Answer 1

select NodeId, S.part from #tmpTable cross apply [dbo].[SplitString](ExternalIds, ',') as S 

Answer 2

Here is a brief example of how the cursor over the data is in the desired format. Please note: this is more of a demo that does not currently use the split function. I would make two comments: - the imo cursor should be avoided - in the form above the commentator suggested trying to avoid storing delimited data

 DECLARE @Table TABLE ( NodeId int, ExternalID int ) DECLARE @NodeId int DECLARE @ExternalIds nvarchar(2000) DECLARE db_cursor CURSOR FOR SELECT NodeId, ExternalIDs from dbo.test OPEN db_cursor FETCH NEXT FROM db_cursor INTO @NodeId, @ExternalIds WHILE @@FETCH_STATUS = 0 BEGIN WHILE (Charindex(',',@ExternalIds)>0) Begin INSERT INTO @Table (NodeId, ExternalId) SELECT @NodeId, Data = ltrim(rtrim(Substring(@ExternalIds,1,Charindex(',',@ExternalIds)-1))) SET @externalids = Substring(@ExternalIds,Charindex(',',@ExternalIds)+1,len(@ExternalIds)) End INSERT INTO @Table (NodeId, ExternalId) Select @NodeId, Data = ltrim(rtrim(@ExternalIds)) FETCH NEXT FROM db_cursor INTO @NodeId, @ExternalIds END CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM @Table