SQL statement which returns id and splits comma separated values
I have a table with the fo开发者_如何学JAVAllowing 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
select NodeId,
S.part
from #tmpTable
cross apply [dbo].[SplitString](ExternalIds, ',') as S
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
精彩评论