SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
BEGIN -- Checking to ensure temp table does not exist, and dropping anyone that does to avoid errors

    DROP TABLE #temp;
END;

--- Create Temp Table to store values and manipulate before inserting into production table ----

CREATE TABLE #temp
(
Code VARCHAR(MAX)
, Keyword VARCHAR(MAX)
, Description VARCHAR(MAX)
, Component_Keys VARCHAR(MAX)
);

INSERT INTO #temp (Code
                 , Keyword
                 , Description
                 , Component_Keys)
VALUES ('A1', 'Sally', 'Sally : Mary is her Daughter', NULL)
     , ('A2', 'Mary', 'Mary : John is her sister and sally is her mother', NULL)
     , ('A3', 'Jane', 'Jane : John is her partner', NULL)
     , ('A4', 'John'
      , 'John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner', NULL);

SELECT *
FROM   #temp;


-- Creating Variables
DECLARE @DKeyword VARCHAR(MAX); -- Declaring Dynamic Keyword Variable
DECLARE @DUpdateStatement VARCHAR(MAX); ---Declaring Dynamic Update Statement Variable
DECLARE @DCode VARCHAR(MAX) -- Declaring Dynamic Code Variable

SELECT @DKeyword= Keyword, @DCode=code
FROM #temp


Code
Keyword
Description
Component_Keys


A1
Sally
Sally : Mary is her Daughter
null

A2
Mary
Mary : John is her sister and sally is her mother
null

A3
Jane
Jane : John is her partner
null

A4
John
John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner
null


UPDATE T
SET Component_Keys = (
    SELECT STRING_AGG(T2.Code, '+') WITHIN GROUP(ORDER BY P.Pos)
    FROM #temp T2
    CROSS APPLY(SELECT CHARINDEX(T2.Keyword, T.Description) AS Pos) P
    WHERE T2.Code <> T.Code
    AND P.Pos >= 1
)
FROM #temp T

SELECT *
FROM   #temp;


Code
Keyword
Description
Component_Keys


A1
Sally
Sally : Mary is her Daughter
A2

A2
Mary
Mary : John is her sister and sally is her mother
A4+A1

A3
Jane
Jane : John is her partner
A4

A4
John
John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner
A3+A1+A2


-- Reset
UPDATE #temp
SET Component_Keys = NULL

UPDATE T
SET Component_Keys = C.ComponentKeys
FROM #temp T
CROSS APPLY (
    SELECT STRING_AGG(T2.Code, '+') WITHIN GROUP(ORDER BY P.Pos) AS ComponentKeys
    FROM #temp T2
    CROSS APPLY(SELECT CHARINDEX(T2.Keyword, T.Description) AS Pos) P
    WHERE T2.Code <> T.Code
    AND P.Pos >= 1
) C

SELECT *
FROM   #temp;


Code
Keyword
Description
Component_Keys


A1
Sally
Sally : Mary is her Daughter
A2

A2
Mary
Mary : John is her sister and sally is her mother
A4+A1

A3
Jane
Jane : John is her partner
A4

A4
John
John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner
A3+A1+A2

Code	Keyword	Description	Component_Keys
A1	Sally	Sally : Mary is her Daughter	null
A2	Mary	Mary : John is her sister and sally is her mother	null
A3	Jane	Jane : John is her partner	null
A4	John	John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner	null