你有一个像
set @string = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
我想知道是否有一种方法可以提取字符串值并将其放置在第一行中,将double值并将其放置在第二行中,并将int值放置在第三行中。这
"string,double,int,string,double,int..."
但在某些情况下
"string,double,int,string,double,string,double,int"
我想在第三行中将int默认设置为1,以便表看起来像这样。
First Row Second Row Third Row
ddd 1.5 1
eee 2.3 0
fff 1.2 1
ggg 6.123 1
我有一个代码,您可以从字符串中提取所有值并将它们放在一行中,但这还不够。
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
)
其余所有与以前的版本相同。
我要做的第一件事是将字符串拆分功能更改为基于理货表格的功能,以便我可以轻松地向其中添加行号。因此,如果您还没有统计表,请创建一个。如果您问自己是什么理货表以及为什么需要它,请阅读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
然后,基于提示表创建字符串拆分函数(摘自Aaron的文章,但添加了行索引列):
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,这基本上是基于行的字符串总数所有行的索引:
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
如您所见,我现在每行都有一个数字,因此从现在开始很简单:
;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
如您所见,项目现在按字符串中的原始顺序排序。感谢您的挑战,自从我有一个不错的人以来已经有一段时间了:-)
好吧,首先,您必须将该字符串拆分为一个表。为此,您应该使用用户定义的函数。您可以以正确的方式从Aaron
Bertrand的Split字符串中选择最适合自己的一种,或者选择第二种最佳方式。
在本演示中,我选择使用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
现在,声明并初始化变量:
declare @string nvarchar(max) = 'ddd,1.5,1,eee,2.3,0,fff,1.2,ggg,6.123,1'
然后,创建4个公用表表达式-一个用于所有项目,一个用于字符串,一个用于双精度,一个用于整数。请注意该row_number()
函数的用法-稍后将使用该函数将所有结果结合在一起:
;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
内置函数仅返回存在至少一个值的行:
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
我在尝试从txt文件中读取字符串和Double时遇到了一些麻烦。这是我的txt文件: 下面是我用来阅读它们的代码: 每当我运行此代码时,线程“main”java.util.InputMisMatchException中会出现
我正在使用一个新的 java 应用程序,该应用程序适用于 db2 中无法更改的旧数据库。在其中一个表中,有包含 comp3 字符的 char(32) 列。 我们使用ibmdb2jdbctype4驱动程序阅读本专栏。当我们检查字节以解压缩字符时,我们发现无论何时bbdd中有一个值为25的字节,jdbc驱动程序都会将其恢复为值15。值为15的字节也会恢复为15的值,因此我们不知道如何解决这个不明确的值
问题内容: 我需要使用T-SQL逐个字符地比较两个字符串。假设我有两个类似的字符串: 每当字符不匹配时,我想增加变量@Diff + = 1。在这种情况下,前三个字符不同。因此,@ Diff = 3(默认值为0)。 感谢您的所有建议。 问题答案: 对于您不想使用逐行方法的表中的列,请尝试以下一种方法: = > SQL小提琴演示
我正在构建一个计算器,为了让计算器工作,我需要将字符串中的所有整数转换为双数。例如,如果我有一个字符串:3*8 5/2-4,我想将其转换为:3.0*8.0 5.0/2.0-4.0。我该怎么做? 编辑:如果我有这个字符串:3.0*8.0 5.0/2.0-4,我想将他转换为:3.0*8.0 5.0/2.0-4.0
如何执行提取sql字符串中字符的ENTER[chr(13)char(10)]的sql查询?谢谢
问题内容: 有一个名为myDate的文本字段。该字段可以包含1)“结束 某日期 的会计年度”或2)“ dateA 到” dateB ”。 在情况1)中,我想将字段date1 =设置为 someDate 。 在情况2)中,我要设置字段date1 = dateA 和字段date2 = dateB 。 所有日期( someDate , dateA , dateB )都可以写为2000年1月1日,2000