Search for string in a text column and list the count

Question

Ok, I may be asking very stupid question but somehow I am not able to get a way to perform the following.

I have a table that contains two columns as below

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| SL No |                                                                      Work                                                                       |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|     1 | Identify Process Champs across all teams for BCUK processes                                                                                     |
|     2 | Impart short training on FMEA to all the Process Champs                                                                                         |
|     2 | List down all critical steps involved in the Process to ascertain the risk involved, feed the details back to FMEA template to analyze the risk |
|     3 | Prioritize the process steps based on Risk Priority Number                                                                                      |
|     4 | Identity the Process Gaps, suggest process improvement ideas to mitigate/mistake proof or reduce the risk involved in the process               |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+

Now I have other table that holds the "Key Words" like below

+-------+----------+
| Sl No |   Tags   |
+-------+----------+
|     1 | BCUK     |
|     2 | FMEA     |
|     3 | Priority |
|     4 | Process  |
+-------+----------+

Now I would like to "Search for String" in first table based on "tags" in second table and return something like this

+----------+-------+
|   Tags   | Count |
+----------+-------+
| BCUK     |     1 |
| FMEA     |     2 |
| Priority |     1 |
| Process  |     8 |
+----------+-------+

As "Process" keyword appears eight times in the entire table (first table) across multiple rows it returns the count as 8.

I am using SQL Server 2014 Express Edition

Answer 1

Adam Machanic has a function GetSubstringCount for this kind of operations. I modified it a bit for your needs. For more info: http://dataeducation.com/counting-occurrences-of-a-substring-within-a-string/

SAMPLE DATA

CREATE TABLE MyTable(
    SLNo    INT,
    Work    VARCHAR(4000)
)
INSERT INTO MyTable VALUES
(1, 'Identify Process Champs across all teams for BCUK processes'),
(2, 'Impart short training on FMEA to all the Process Champs'),
(2, 'List down all critical steps involved in the Process to ascertain the risk involved, feed the details back to FMEA template to analyze the risk'),
(3, 'Prioritize the process steps based on Risk Priority Number'),
(4, 'Identity the Process Gaps, suggest process improvement ideas to mitigate/mistake proof or reduce the risk involved in the process');

CREATE TABLE KeyWord(
    SLNo    INT,
    Tag     VARCHAR(20)
)
INSERT INTO KeyWord VALUES
(1, 'BCUK'),
(2, 'FMEA'),
(3, 'Priority'),
(4, 'Process');

SOLUTION

;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,E2 AS(SELECT 1 AS N FROM E1 a, E1 b)
,E4 AS(SELECT 1 AS N FROM E2 a, E2 b)
,Tally(N) AS(
    SELECT TOP(11000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))FROM E4 a, e4 b
)
SELECT
    k.Tag,
    [Count] = SUM(x.cc)
FROM KeyWord k
CROSS JOIN MyTable m
CROSS APPLY(
    SELECT COUNT(*) AS cc
    FROM Tally
    WHERE
        SUBSTRING(m.Work, N, LEN(k.tag)) = k.tag
)x
GROUP BY k.tag

RESULT

Tag                  Count
-------------------- -----------
BCUK                 1
FMEA                 2
Priority             1
Process              8