SQL statement which returns id and splits comma separated values

7
votes

I have a table with the following data:

NodeId ExternalIds
50     601
56     700,701

How do I write an SQL-statement which splits the ExternalIds-column and returns:

NodeId ExternalIds
50     601
56     700
56     701

I have found a lot of user defined functions and procedure which splits a string into a table, but I cannot get any of them to work

edit

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 the database for Umbraco (the cms), which is designed with the comma-separated values.

thanks Thomas

2
sql-server
please show what happened when you tried to get these functions to work. – Hogan
"I have found a lot of user defined functions and procedure which splits a string into a table, but I cannot get any of them to work" what have you tried? – Conrad Frix
Perhaps you should properly design your database. Data should almost NEVER be stored in a delimited list. – HLGEM
@HLGEM. Perhaps this is an effort to migrate the data to a table that is in 1NF? – Conrad Frix
Hi. I updated the question with my current sql – ThomasD

2 Answers

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

Here is a quick example of how to cursor over the data into your desired format. Note, this is more of a quick demo that doesn't currently use a split function. I would make two comments: - imo cursor's should be avoided - as above commenter's have suggested try 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