Pattern matching techniques in SQL to correctly match a sting to a keyword but not getting the right results

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:

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 [ ]?

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], ' ', '%') + '%'

Advertisement

Answer