计算字符串 T-SQL 中单独出现的次数

发布于 2025-01-11 04:26:31 字数 962 浏览 0 评论 0原文

我需要帮助计算在字符串中找到“模式”的次数。我正在使用一个工具将输入的模式提取到列中。我的日期集如下所示：

Id	MatchedPatterns
1	“[模式一：SE09B00218]，[模式二：0000000-09000,0974837-01]”
2	“[模式一：SE09B00218，DA02F11418]，[模式二： 0000000-09,0974837-01]"
3	"[模式一：TR38G01678,DA02F11418,KJ73F4567]"
4	"[模式二： 0000009-00000007,1234567-02,0939485-0004,09836473-1]"

您可以看到每个模式可以找到多次（最多 200 次），用逗号分隔，也可以为空。所识别的每个图案也可以具有不同的长度。

我希望输出如下所示：

Id	PatternOneCount	PatternTwoCount
1	1	2
2	2	2
3	3	0
4	0	4

这可能吗？

非常感谢

原文

I need help counting how many times a 'pattern' is found within a string. I'm using a tool which extracts inputted patters into a column. My dateset looks like the following:

Id	MatchedPatterns
1	"[Pattern One: SE09B00218], [Pattern Two: 0000000-09000,0974837-01]"
2	"[Pattern One: SE09B00218,DA02F11418], [Pattern Two: 0000000-09,0974837-01]"
3	"[Pattern One: TR38G01678,DA02F11418,KJ73F4567]"
4	"[Pattern Two: 0000009-00000007,1234567-02,0939485-0004,09836473-1]"

You can see that each pattern can be found multiple times (up to 200), separated by a comma or it can be null. Each pattern identified can also be different lengths.

Here is what I want the output to look like:

Id	PatternOneCount	PatternTwoCount
1	1	2
2	2	2
3	3	0
4	0	4

Is this possible?

Many thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

云醉月微眠 2025-01-18 04:26:31

假设您使用的是（完全）受支持的 SQL Server 版本，您可以将数据转换为有效的 JSON，然后使用它。

因此，首先用双引号 (") 替换方括号 ([])，然后将冒号括起来 (:) 也用双引号括起来，然后将整个字符串括在大括号 ({}) 中，以获取 JSON 字符串，

然后使用 OPENJSON 使用该 JSON，使用STRING_SPLIT来分割分隔数据，然后最后使用条件聚合...恶心。

SELECT V.Id,
       COUNT(CASE J.[key] WHEN 'Pattern One' THEN SS.[value] END) AS PatternOne,
       COUNT(CASE J.[key] WHEN 'Pattern Two' THEN SS.[value] END) AS PatternTwo
FROM (VALUES(1,'[Pattern One: SE09B00218], [Pattern Two: 0000000-09000,0974837-01]'),
            (2,'[Pattern One: SE09B00218,DA02F11418], [Pattern Two: 0000000-09,0974837-01]'),
            (3,'[Pattern One: TR38G01678,DA02F11418,KJ73F4567]'),
            (4,'[Pattern Two: 0000009-00000007,1234567-02,0939485-0004,09836473-1]'))V(Id,MatchedPatterns)
     --Make the pattern valid JSON
     CROSS APPLY (VALUES(REPLACE(TRANSLATE(V.MatchedPatterns,'[]','""'),': ','":"')))R(JsonPatterns)
     CROSS APPLY OPENJSON(CONCAT('{',R.JsonPatterns,'}')) J
     OUTER APPLY STRING_SPLIT(J.[value],',') SS
GROUP BY V.Id;

Assuming you're on a (fully) supported version of SQL Server, you could transform your data into valid JSON, and then consume it.

So first you REPLACE the brackets ([]) with double quotes ("), and the wrap the colons (:) with double quotes as well. Then you wrap the entire string in braces ({}), to get your JSON string.

Next you consume that JSON with OPENJSON, use STRING_SPLIT to split the delimited data, and then finally use conditional aggregation... yuck.

SELECT V.Id,
       COUNT(CASE J.[key] WHEN 'Pattern One' THEN SS.[value] END) AS PatternOne,
       COUNT(CASE J.[key] WHEN 'Pattern Two' THEN SS.[value] END) AS PatternTwo
FROM (VALUES(1,'[Pattern One: SE09B00218], [Pattern Two: 0000000-09000,0974837-01]'),
            (2,'[Pattern One: SE09B00218,DA02F11418], [Pattern Two: 0000000-09,0974837-01]'),
            (3,'[Pattern One: TR38G01678,DA02F11418,KJ73F4567]'),
            (4,'[Pattern Two: 0000009-00000007,1234567-02,0939485-0004,09836473-1]'))V(Id,MatchedPatterns)
     --Make the pattern valid JSON
     CROSS APPLY (VALUES(REPLACE(TRANSLATE(V.MatchedPatterns,'[]','""'),': ','":"')))R(JsonPatterns)
     CROSS APPLY OPENJSON(CONCAT('{',R.JsonPatterns,'}')) J
     OUTER APPLY STRING_SPLIT(J.[value],',') SS
GROUP BY V.Id;

回复收藏 0 原文

~没有更多了~