如果您正在使用SQL Server 2016,那么您可以像我在上面的评论中所述一样使用string_split函数。
select * from string_split('1,2,3,4,5',',')
然而:
如果您使用的是早期版本的 SQL Server < 2016,则可以创建一个返回表的函数,然后从那里加入到您需要更新的表中:
CREATE FUNCTION dbo.SplitStringToValues
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E42(N) AS (SELECT 1 FROM E4 a, E2 b),
cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1)))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s;
go
然后这样使用函数
SELECT * FROM SplitStringToValues('1,2,3,5',',') as t
更新:
根据您在评论中提出的要求,为了获取id旁边的下一行值,您可以像下面这样做。它使用了SQL SERVER 2012中引入的LEAD函数:
SELECT Id, Value
FROM (SELECT
ROW_NUMBER() over (order by(select 1)) as cnt,
t.item AS Id,
Lead(t.item)
OVER (
ORDER BY (SELECT 1)) Value
FROM dbo.Splitstringtovalues('10,20,30,40,50,10,20,30,40,50,60,70', ',')
t)
keyValue
WHERE keyValue.value IS NOT NULL
and cnt % 2 = 1
BULK INSERT
创建一个将该文件导入临时表的过程应该更容易,并且可以按照您想要的方式处理数据。 - Joao Araujo