从sql中的字符串读取char，double，int模式 [英] Read char,double,int pattern from string in sql
问题描述
你有一个类似的字符串
set @string = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
我想知道是否有办法提取字符串值并将它们放在第一行，双精度值并将它们放在第二行和整数值并将它们放在第三行.字符串逻辑是这样的
I would like to know if there is a way to extract the string values and place them in the first row,the double values and place them in the second row and the int values and place them in the third row.The string logic is like this
"string,double,int,string,double,int..."
但也有这种情况
"string,double,int,string,double,string,double,int"
并且我希望在第三行中，默认情况下 int 应为 1，以便表格看起来像这样.
and I would like in the third row where the int should be to be 1 by default so the table would look something like this.
First Row Second Row Third Row
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 1
我有一个代码，您可以在其中提取字符串中的所有值并将它们排成一行，但这还不够.
I have a code where u can extract all the values from the string and place them in a row but that is not enough.
declare @string as nvarchar(MAX)
set @string = 'aaa,bbb,ccc,ddd,1.5,1,eee,2.3,1,fff,1.2,ggg,6.123,1'
;with tmp(DataItem, Data)
as (
select LEFT(@string, CHARINDEX(',',@string+',')-1),
STUFF(@string, 1, CHARINDEX(',',@string+','), '')
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > '')
select DataItem from tmp
option (maxrecursion 0)
推荐答案
最终版本(我希望):
由于 sql server 2008 在聚合函数的 over 子句中不支持 order by，我添加了另一个 cte 来添加行索引而不是我在前面使用的 sum版本:
;WITH cteAllRows as
(
SELECT Item,
ItemIndex,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteAll as
(
SELECT Item,
DataType,
ItemIndex,
(
SELECT COUNT(*)
FROM cteAllRows tInner
WHERE tInner.DataType = 'String'
AND tInner.ItemIndex <= tOuter.ItemIndex
) As RowIndex
FROM cteAllRows tOuter
)
其余的和之前的版本一样.
All the rest is the same as the previous version.
我做的第一件事是将字符串拆分函数更改为基于计数表的函数，以便我可以轻松地向其中添加行号.因此，如果您还没有理货表，创建一个.如果您问自己什么是计数表以及为什么需要它，阅读Jeff Moden 撰写的这篇文章:
The first thing I've done is to change the string split function to a function based on a tally table, so that I can easily add the row number to it. So, if you don't already have a tally table, create one. If you are asking your self what is a tally table and why do you need it, read this article by Jeff Moden:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Tally
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Tally ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
GO
然后，根据tally表创建字符串拆分函数(取自Aaron的文章，但添加了行索引列):
Then, create string split function based on the tally table (taken from Aaron's article but added the row index column):
CREATE FUNCTION dbo.SplitStrings_Numbers
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number),
ROW_NUMBER() OVER (ORDER BY Number) As ItemIndex
FROM dbo.Tally
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
);
GO
现在，我使用的技巧与前一个非常相似，只是现在我在第一个 cte 中添加了一个名为 RowIndex 的新列，这基本上是字符串计数的总和，基于在所有行的行索引上:
Now, The trick I've used is very much like the previous one, only now I've added to the first cte a new column I've called RowIndex, that's basically a running total of the count of strings, based on the row index of all rows:
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
它给了我这个结果:
Item DataType RowIndex
---------- -------- -----------
ddd String 1
1.5 Double 1
1 Integer 1
eee String 2
2.3 Double 2
0 Integer 2
fff String 3
1.2 Double 3
ggg String 4
6.123 Double 4
1 Integer 4
如您所见，我现在每行都有一个数字，所以从现在开始很简单:
As you can see, I now have a number for each row, so from now on it's simple:
;WITH cteAll as
(
SELECT Item,
CASE WHEN ISNUMERIC(Item) = 0 THEN 'String'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0 THEN 'Double'
WHEN ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0 THEN 'Integer'
END As DataType,
SUM(CASE WHEN ISNUMERIC(Item) = 0 THEN 1 END) OVER(ORDER BY ItemIndex) As RowIndex
FROM dbo.SplitStrings_Numbers(@string, ',')
), cteString AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'String'
), cteDouble AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Double'
), cteInteger AS
(
SELECT Item, RowIndex
FROM cteAll
WHERE DataType = 'Integer'
)
SELECT T1.Item As [String],
T2.Item As [Double],
T3.Item As [Integer]
FROM dbo.Tally
LEFT JOIN cteString T1 ON T1.RowIndex = Number
LEFT JOIN cteDouble T2 ON t2.RowIndex = Number
LEFT JOIN cteInteger T3 ON t3.RowIndex = Number
WHERE COALESCE(T1.Item, T2.Item, T3.Item) IS NOT NULL
这给了我这个结果:
String Double Integer
---------- ---------- ----------
ddd 1.5 1
eee 2.3 0
fff 1.2 NULL
ggg 6.123 1
如您所见，项目现在按字符串中的原始顺序排序.感谢您的挑战，我已经有一段时间没有像样的了 :-)
As you can see, the items are now sorted by the original order in the string. Thanks for the challenge, It's been a while since I've had a decent one :-)
好吧，首先您必须将该字符串拆分为一个表格.为此，您应该使用用户定义的函数.您可以从 Aaron Bertrand 的 Split strings the right 中选择最适合您的方式——或次优方式文章.
Well, first you have to split that string into a table. To do that you should use a user defined function. You can pick the one best suited for you from Aaron Bertrand's Split strings the right way – or the next best way article.
对于这个演示，我选择使用 SplitStrings_XML.
For this demonstration I've chosen to use the SplitStrings_XML.
首先，创建函数:
CREATE FUNCTION dbo.SplitStrings_XML
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
FROM
(
SELECT x = CONVERT(XML, '<i>'
+ REPLACE(@List, @Delimiter, '</i><i>')
+ '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
);
GO
现在，声明并初始化变量:
Now, declare and initialize the variable:
declare @string nvarchar(max) = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
然后，创建 4 个通用表表达式 - 一应俱全项，一项用于字符串，一项用于双精度数，一项用于整数.请注意 row_number() 函数 - 稍后将用于将所有结果连接在一起:
Then, Create 4 common table expressions - one for all items, one for strings, one for doubles and one for integers. Note the use of the row_number() function - it will be used later to join all the results together:
;WITH AllItems as
(
SELECT Item, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
)
, Strings as
(
SELECT Item as StringItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 0
), Doubles as
(
SELECT Item as DoubleItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) > 0
), Integers as
(
SELECT Item as IntegerItem, ROW_NUMBER() OVER(ORDER BY (select null)) as rn
FROM dbo.SplitStrings_XML(@string, ',')
WHERE ISNUMERIC(Item) = 1 AND CHARINDEX('.', Item) = 0
)
然后，从连接所有这些公用表表达式中进行选择.注意 COALESCE 的使用在函数中只返回至少存在一个值的行:
Then, select from joining all these common table expressions. Note the use of the COALESCE built in function to only return rows where at least one value is present:
SELECT StringItem, DoubleItem, IntegerItem
FROM AllItems A
LEFT JOIN Strings S ON A.rn = S.rn
LEFT JOIN Doubles D ON A.rn = D.rn
LEFT JOIN Integers I ON A.rn = I.rn
WHERE COALESCE(StringItem, DoubleItem, IntegerItem) IS NOT NULL
结果:
StringItem DoubleItem IntegerItem
---------- ---------- -----------
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 NULL
这篇关于从sql中的字符串读取char，double，int模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
