更新时间:2022-01-12 23:09:45
首先创建一个拆分函数:
First, create a split function:
CREATE FUNCTION dbo.SplitStrings
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);
GO
现在您可以简单地推断:
Now you can extrapolate simply by:
SELECT s.[message-id], f.Item
FROM dbo.SourceData AS s
CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;
另外我建议不要在列名中放置破折号.这意味着您必须始终将它们放在 [方括号]
中.
Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets]
.