How to pair up the same strings in two table where in one of them they have a '?' replacing some of the characters?

I have names of people in a table as primary keys and I have them in another as foreign keys except in this second table they have been occasionally recorded faultily, with a ‘?’ symbol replacing a character. I’m looking to eventually update this second table with the correct names, but for now I would just like to select a list of faulty names paired up with the respective correct names. I came up with the idea to replace every ‘?’ with an ‘_’ character in the names hoping it would act as the wildcard character marking the place of exactly one missing character so I could join the two tables on a LIKE statement. However when I run the SQL query below I get zero rows returned (even though I know there should be plenty):

SELECT
    T1.Name,
    T2.Name
FROM
    table1 T1
INNER JOIN
    table2 T2
ON
    T1.Name LIKE REPLACE(T2.Name, '?', '_')
WHERE
    T2.NAME REGEXP '[?]';

I’m suspecting it treats the ‘_’ symbol as part of the string instead of a wildcard character? If that’s the case, how should I write it to get the correct results?

You need to sign in to view this answers