search

Home  >  Q&A  >  body text

Regular expression to match words

I have a script where I am trying to match new job names with existing job names 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 for the new position, in this case Software Developer,USA. I want to match it to the database's existing job title "Software Developer". The regex code above works for some positions but not others. Please help develop a more comprehensive plan.

I am using mysql V8.

P粉957723124P粉957723124229 days ago885

reply all(1)I'll reply

  • P粉509383150

    P粉5093831502024-04-07 00:55:41

    • You don't need to test the beginning/end of the string; these are "word boundaries".

    • MySQL 8.0 uses \b as two word boundaries instead of [[:<:]] and [[:>:]]

    • In some cases, 8.0 requires doubling backslashes.

    This may work:

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

    reply
    0
  • Cancelreply