I have a table (dbo.Library) where I have build a library of keywords that are associated with different titles and persona in a company. See below:
x
CREATE TABLE dbo.Library
(ID INT IDENTITY(1,1),
Persona VARCHAR(20),
Keyword VARCHAR(100)
)
INSERT INTO dbo.Library
(Persona,
Keyword)
SELECT 'CMO', 'Digital Marketing'
UNION SELECT 'CMO', 'Marketing Operation'
Let’s say I have another table as below:
CREATE TABLE dbo.TargetTable
(ID INT IDENTITY(1,1),
Title VARCHAR(20),
Persona VARCHAR(100)
)
INSERT INTO dbo.TargetTable
(Title)
SELECT 'Director of Digital transformation in Marketing'
UNION SELECT 'Digital Marketing Analyst'
UNION SELECT 'Marketing and Sales Operation'
Now I want to JOIN the dbo.library
to the dbo.TargetTable
on title using pattern matching to update the Persona column accordingly:
UPDATE A
SET Persona = B.Persona
FROM dbo.TargetTable A
INNER JOIN dbo.Library B
ON title LIKE '%' + [Keyword] + '%'
This matches on every letter in the keyword hence I get titles that have nothing to the keyword matched to a persona. While what I actually want is:
UPDATE A
SET Persona = B.Persona
FROM dbo.TargetTable A
INNER JOIN dbo.Library B
ON Title LIKE '%' + 'Digital' + '%' + 'Marketing' + '%'
Do you know how can I solve this? Is there even a way to do exact matching of the keyword string while using [ ]?
Advertisement
Answer
How about simply replacing embedded spaces in the keyword with wildcard character '%'
?
UPDATE A
SET Persona = B.Persona
FROM dbo.TargetTable A
INNER JOIN dbo.Library B ON title LIKE '%' + REPLACE([Keyword], ' ', '%') + '%'