Lets say I have a table in SQL
| Code | Keyword | Description | Component_Keys |
|---|---|---|---|
| A1 | Sally | Sally : Mary is her Daughter | |
| A2 | Mary | Mary : John is her sister and sally is her mother | |
| A3 | Jane | Jane : John is her partner | |
| A4 | John | John : Jane is his partner and Sally is his mother and Mary is his sister. Mary is not his partner |
I would like to find a way to dynamically update Component_Keys such that it loops through each description, identifies an instance of the Keyword within the description , and records the Code in the column Component_Keys if it finds an instance of the Keyword. If a keyword appears more than once in the description, I would like it to appear only once in Component_Keys. If multiple keywords appear I would like it to be recorded as Code1+Code2+Code3…..
Lastly, I would like it to ignore the keyword in the description if it is in the same row.
As such the output of the update query would look like this
| 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 |
Thomas Short is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.