查询返回特定值在字符串中出现的次数? [英] Query to return the number of times a specific value occurs within a string?

问题描述

好吧，我有两张桌子.表 1 具有参考编号列 (A)，以及具有随机性字符串 (B) 的第二列.表 2 只有一列，其中包含一系列值，这些值可能在也可能不在 Table1 的字符串中.

Alright so I have two tables. Table1 has an reference number column (A), and a second column that has a string of randomness (B). Table2 has only one column with a list of values that may or may not be in the string on Table1.

dbo.Tbl_1

+--+---------------------------------------+
|A |B                                      |
+--+---------------------------------------+
|24|BLUE; KITTEN; WHITE; PINK; SLOPE; GREEN|
+--+---------------------------------------+
|51|GREEN; CLOUDY; WHITE; CHIPS            |
+--+---------------------------------------+
|78|PATRIOTS; PINK; PINK; WHITE            |
+--+---------------------------------------+
|22|WHITE; RED; TREES; AMY; GREEN          |
+--+---------------------------------------+

dbo.Tbl_2

+-----+
|C    |
+-----+
|BLUE |
+-----+
|WHITE|
+-----+
|PINK |
+-----+
|BROWN|
+-----+

哪个 sql 查询将确定在 Table1 的字符串中找到来自 Table2 的值多少次?基本上我想返回以下结果集:

What sql query would determine how many times a value from Table2 is found in the string on Table1? Basically I want to return the below result set:

+-----+----+
|BLUE |1   |
+-----+----+
|WHITE|4   |
+-----+----+
|PINK |3   |
+-----+----+
|BROWN|NULL|
+-----+----+

仅供参考:实际上，Table2 有大约 200 条唯一记录.表 1 有大约 160 万条具有唯一参考号的记录.两个表都不是静态的.

FYI: In reality, Table2 has about 200 unique records. Table1 has about 1.6M records with unique reference numbers. Neither tables are static.

推荐答案

我玩了一会儿，想出了这个 SQL 小提琴

I played around a bit and came up with this SQL fiddle

相关的 SELECT 查询如下所示(虽然需要两次表扫描，但我相信它可以提高效率):

The relevant SELECT query looks like this (requires two table scans though, I'm sure it can be made more efficient):

select C, sum(dbo.CountOccurancesOfString(B, C)) as number
from Tbl_1 join Tbl_2 on 1=1
group by C
order by number desc

EDIT 这是我从 这个答案:

CREATE FUNCTION dbo.CountOccurancesOfString
(
 @searchString nvarchar(max),
 @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
 return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END

这篇关于查询返回特定值在字符串中出现的次数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！