使用多个定界符解析sql字符串以获取整数值 [英] parsing a sql string for integer values with multiple delimiters

问题描述

我在SO上找到了一些相关示例，但没有一个专门针对这种情况的示例.去吧:

I found some related examples here on SO, but not one that for this case specifically. Here goes:

我需要使用SQL解析从管道分隔的平面文件中获取的数据.字段之一的子格式如下.我的最终状态是对字段中的整数求和，但是我的问题是查看使用SQL SELECT解析/提取整数的方法.子格式的模式将始终是所需的整数，在其前面将出现一个代字号(〜)，然后是一个星号(*).子字段的数量也可能有所不同(我的示例为5，但可能更多或更少). 4个字符的TAG名称并不重要.

I need to use SQL to parse data sourced from a flat file that is pipe delimited. One of the fields is sub-formatted as follows. My end state is to sum the integers within the field, but my question here is to see of ways to use a SQL SELECT to parse/extract JUST the integers. The pattern of the sub-formatting will always be where the desired integers will be preceded by a tilde (~) and followed by an asterisk (*). The number of sub fields may vary too (my example has 5, but there could more or less). The 4 char TAG name is of no importance.

这是一个示例:

|GADS~55.0*BILK~0.0*BOBB~81.0*HETT~32.0*IGGR~51.0|

在这个例子中，我想要处理的是最终数字219.同样，我可以继续求和部分，这是下一步.只是对获得数字感兴趣.

From this example, all I would want for processing is the final number of 219. Again, I can work on the sum part as a further step; just interested in getting the numbers.

我知道如何使用UNIX工具(即AWK，sed等)非常轻松地处理此问题，但是在SQL中却不是.任何建议都会有所帮助！谢谢！

I know how to handle this quite easily with UNIX tools (ie AWK, sed, etc), but not in SQL. Any suggestions will help! Thanks!

推荐答案

这将返回219:

• 在Cut1中，我让字符串以第一个数字开头.
• 在Cut2中，我剪切了最后一个管道并添加了一个星号
• 在Splitted中，我将〜替换为XML标记，从而使拆分字符串变得容易
• 在最终选择中，将纯数字相加...

现在输入代码

 DECLARE @str VARCHAR(100) = '|GADS~55.0*BILK~0.0*BOBB~81.0*HETT~32.0*IGGR~51.0|';

 WITH Cut1 AS
(
    SELECT SUBSTRING(@str,CHARINDEX('~',@str,1)+1,1000) AS c1
)
,Cut2 AS
(
    SELECT SUBSTRING(Cut1.c1,1,LEN(Cut1.c1)-1) + '*' AS c2 FROM Cut1
)
,Splitted AS
(
    SELECT CAST('<x>' + REPLACE(Cut2.c2,'~','</x><x>') + '</x>' AS XML) AS AsXML FROM Cut2
)
SELECT SUM(CAST(SUBSTRING(numbers.value('.','varchar(100)'),1,CHARINDEX('*',numbers.value('.','varchar(100)'),1)-1) AS FLOAT))
FROM Splitted
CROSS APPLY AsXML.nodes('/x') AS the(numbers)

这篇关于使用多个定界符解析sql字符串以获取整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！