Sql如何将转换拆分字符串插入以'@'分隔的列 [英] Sql how to insert convert split string to columns separated by '@'

问题描述

有点像这样：

SOMETHING LIKE THIS :

INSERT INTO @MyTableVar (C1,C2,C3,C4,C5,C6,C7,C8,C9) select @C1,@C2,@C3,@C4,@C5,@C6,@C7,@C8,@C9 from [dbo].[fnSplit](@MyStr, '@')

问题是，在我的情况下，它成为一个列我希望将子集插入一个字段（行或行）那张桌子



感谢高级^ _ ^



我尝试了什么：

THE PROBLEM IS THAT IT COMES AS ONE COLUMN WHILE IN MY CASE I WANT TO INSERT SUBSTRINGS TO BE ONE FIELD (LINE OR ROW) IN THAT TABLE

THANKS IN ADVANCED ^_^

What I have tried:

declare @MyStr Nvarchar(MAX);
SET @MyStr='ID@MESSAGE@IATA@FlightSuffix@DATE@NOTE@action@DATI@1'
SET @ID = Substring(@MyStr, 1,(Charindex('@', @MyStr)-1));
SET @REMAIN = Substring(@MyStr, Charindex('@', @MyStr)+1, LEN(@MyStr));

推荐答案

看看这里：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]

你会发现一种如何创建函数将字符串拆分成部分的方法。



另一种方法是创建 CTE [ ^ ]。检查我过去的答案：

分开同一列的多个值 [ ^ ]

通过使用T-Sql分割长字符串来获取第2和第3个字符串 [ ^ ]



您需要做的就是将分隔符从[，]更改为[ @ ]



注意，SQL Server 2016具有内置功能： STRING_SPLIT（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]

Take a look here: Using comma separated value parameter strings in SQL IN clauses[^]
There you'll find a way how to create a function to split string into parts.

Another way is to create CTE[^]. Check my past answers:
separate multiple values for the same column[^]
get 2nd and 3rd string by splitting a long string using T-Sql[^]

All you need to do is to change a separator from [,] to [@]

Note, that SQL Server 2016 has got in-built function: STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

您是否可能想要从SPLIT中获取PIVOT表？

我所在的实例没有SQL Split_String函数，只有一个列函数...您可能需要根据您的机器设置进行调整

Could you possibly be wanting a PIVOT table being sourced from a SPLIT?
The instance I am on does not have the SQL Split_String function, only a single column function... you may need to adjust depending on your machines setup

DECLARE @entry NVARCHAR(100) = 'c1@c2@c3@c4@c5@c6@c7@c8@c9'
DECLARE @parse TABLE (RowN INT identity(1,1) NOT NULL, element NVARCHAR(100))

INSERT  @parse (element)
SELECT  Val
FROM    dbo.fnSplitString(@entry, '@')

SELECT  ColumnName = 'data', [1], [2], [3], [4], [5], [6], [7], [8]
FROM	(  SELECT  RowN, element
           FROM    @parse									 
) AS SourceTable

PIVOT	(  Max(Element)
           FOR RowN IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PivotTable

将返回

Which will return

ColumnName  1   2   3   4   5   6   7   8
----------  --  --  --  --  --  --  --  --
data        c1  c2  c3  c4  c5  c6  c7  c8

这篇关于Sql如何将转换拆分字符串插入以'@'分隔的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！