SQL拆分制表符分隔列
我在包含标题字段及其相关结果的数据库中有一个制表符分隔的列。我需要一个查询协助,这个查询会根据IP列在单独的行中返回这些数据。以下是数据的样本,也是期望的结果。SQL拆分制表符分隔列
下面的查询产生这个结果。
DECLARE @Tmp TABLE (ID int Identity(1,1), IP nvarchar(255),Results NVARCHAR(max));
INSERT into @Tmp select top 1 ip, Results from MyTable where results like 'Package%'
select * from @Tmp
这是期望的结果。
IP Package Installed Version Required Version
10.48.8.28 nss 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
10.48.8.28 nss-sysinit 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
10.48.8.28 nss-tools 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
10.48.8.28 nss-util 3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3
这是从SQL结果窗口复制到剪贴板的数据。
ID IP Results
1 10.46.8.28 Package Installed Version Required Version
python 2.7.5-39.el7_2.x86_64 2.7.5-48.el7
python-libs 2.7.5-39.el7_2.x86_64 2.7.5-48.el7
创建示例数据
Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max))
Insert Into @YourTable values
(1,'444.333.222.11','Package Installed Version Required Version
nss 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-tools 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-util 3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3')
它看起来像这样
方案1(与解析功能)
Select A.ID
,A.IP
,Package = Pos1
,[Installed Version] = Pos2
,[Required Version] = Pos3
From @YourTable A
Cross Apply [dbo].[udf-Str-Parse](A.TabString,char(13)) B
Cross Apply [dbo].[udf-Str-Parse-Row](B.RetVal,char(9)) C
Where B.RetVal is not null and B.RetSeq>1
返回
选项2(不解析功能)
Select A.ID
,A.IP
,Package = Pos1
,[Installed Version] = Pos2
,[Required Version] = Pos3
From @YourTable A
Cross Apply (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(A.TabString,char(13),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
Cross Apply x.nodes('x') AS B(i)
) B
Cross Apply (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
From (Select Cast('<x>' + replace((Select replace(B.RetVal,char(9),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as X
) C
Where B.RetVal is not null and B.RetSeq>1
返回
如果有兴趣
CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')
CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
From (Select Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A
)
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John <test> Cappelletti',' ')
--Select * from [dbo].[udf-Str-Parse-Row]('A&B;C;D;E, F;<x>',';')
约翰谢谢你,这个解决方案让我进入了球场,但它基于数据库中的原始数据有点偏离。我附上了由DB产生的实际结果的图片。 –
@EddieD简易修复。给我一点时间。该图像是非常有帮助的 –
@EddieD查看更新的答案 –
如果你使用SQL Server 2016可以使用STRING_SPLIT()
据阿龙贝特朗这是最快的方式解析功能。看到这个https://sqlperformance.com/2016/03/sql-server-2016/string-split
Declare @YourTable table (ID int,IP varchar(50),TabString varchar(max));
Insert Into @YourTable values
(1,'444.333.222.11','Package Installed Version Required Version
nss 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-sysinit 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-tools 3.21.0-9.el7_2.x86_64 3.21.3-2.el7_3
nss-util 3.21.0-2.2.el7_2.x86_64 3.21.3-1.1.el7_3');
;WITH CTE_Lines AS
(
select ID, IP, a.ROWID , a.Lines from @YourTable
CROSS APPLY (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROWID, value Lines
FROM STRING_SPLIT(TabString, CHAR(13)) --Splits by new lines
) a
), CTE_Columns AS
(
SELECT ID, IP, ROWID, b.ColsID, b.[Cols] FROM CTE_Lines
CROSS APPLY (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColsID,
value [Cols]
FROM STRING_SPLIT(Lines, CHAR(9)) --Splits by tabs
) b
WHERE ROWID <> 1 --Removes the headers
)
SELECT ID, IP, [1] Package, [2] [Installed Version],[3] [Required Version] FROM
(
SELECT ID, IP, ROWID, ColsID, [Cols] FROM CTE_Columns
) a
PIVOT (MAX(a.Cols) FOR ColsId in ([1],[2],[3])) as pvt
你的例子对我来说并不完美,但对我来说却是有用的指针。 – Peter
你可以发布你的样品数据作为消耗品吗?不可能告诉你在那里发生了什么。那两列?列分离在哪里?为什么只有第一行有ip? –
@SeanLange我已经用照片更新了帖子,希望增加更多的清晰度。 –