'REGEXP word match

I have a script where I am attempting to match a new jobtitle to an existing one in the database.

SELECT 
a.title AS JobTitle,
j.Description  AS MatchedJobTitle,
f.Description      AS Family,
p.ShortDescription AS ColourComplexity,
j.IsCustomerFacing,
j.JobTitleID
FROM JobTitle j
CROSS JOIN Staging.TMP_OC1 a
INNER JOIN JobFamily f ON j.JobFamilyId = f.JobFamilyID
INNER JOIN Pathways p ON f.PathwaysID = p.PathwaysID        
WHERE a.title REGEXP CONCAT('([[:<:]]|^)', j.Description, '[s]?([[:>:]]|$)');

The Staging.TMP_OC1 table has one record which is the new job title, in this case Software Developer,USA. I would like to match it to the dbs existing job title which is 'software developer'. The regexp code above works for some job titles but not others. Please assist in developing a more comprehensive one.

I am using mysql V8.

mysqlregexmatch


Solution 1:[1]

  • You don't need to test for beginning/ending of string; those are "word boundaries".

  • MySQL 8.0 uses \b for both word boundaries, instead of [[:<:]] and [[:>:]]

  • In some situations, 8.0 needs the backslashes doubled up.

This may work:

REGEXP CONCAT('\\b', j.Description, 's?\\b')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rick James

Related Questions