我有一个包含以下数据的表:
NodeId ExternalIds
50 601
56 700,701
如何编写分割ExternalIds列并返回的SQL语句:
NodeId ExternalIds
50 601
56 700
56 701
我发现了很多用户定义的函数和过程,它们将字符串拆分成表,但我无法让它们中的任何一个工作
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
其中 SplitString 基于以下内容:
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
我正在尝试从 Umbraco(cms)的数据库中获取一些数据,该数据库是用逗号分隔值设计的。
谢谢
托马斯