sql - 如何查询单个 SQL 列并仅获取字符串的特定部分

Question

我正在尝试查询并显示列中的所有唯一 BillingCode，并获取每个 BillingCode 显示的总次数。

问题是，BillingCode 没有分成自己的列，而是列中字符串的一部分，该字符串可能更长或更短，具体取决于帐户的设置。

保持不变且可能有帮助的一点是，计费代码本身包含在标签 12345 内

我试图仅返回 RawXml 列中这些标记之间包含的内容

.

我在自己的测试 SQL 服务器上设置了这个，试图解决这个问题，但到目前为止我还没有运气解决这个问题。

这是一个用于创建新表的 SQL 查询，其中包含我正在使用的示例

CREATE TABLE mytable
    (Column1 varchar(100))
;

INSERT INTO mytable
    (Column1)
VALUES
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing12</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>TEST</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing3</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>123</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>12345</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description>Line 1</Description><BillingCode>BillingCodeHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing1</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>SomethingHere</BillingCode></Xml>'),
('<Xml><Description /><BillingCode>Billing2</BillingCode></Xml>')

.

我认为这是我最接近获得帐单代码的情况。我感觉我已经很接近了，但我一直被困在这里。

Select Right(RawXml, LEN(RawXml) - CHARINDEX('<BillingCode>', RawXml, 1) - 12)
From dbo.mytable

结果集看起来像这样

Billing1</BillingCode></Xml>
Billing12</BillingCode></Xml>
TEST</BillingCode></Xml>
BillingCodeHere</BillingCode></Xml>

.

我希望输出正是计费代码标签之间包含的内容???

从这里，我将能够获得每个唯一帐单代码的总数。

BillingCode     Total
TEST            1
SomethingHere   1
BillingCodeHere 2
Billing1        4
Billing2        3
Billing3        1

etc...

Answer 1

使用 XML 方法:

SELECT BillingCode, COUNT(*)
FROM  mytable
CROSS APPLY (SELECT CAST(Column1 AS XML)) u(v)
CROSS APPLY u.v.nodes('//BillingCode') s(c)
CROSS APPLY (SELECT s.c.value('.', 'nvarchar(128)')) AS z(BillingCode)
GROUP BY BillingCode;

<强> db<>fiddle demo