Search code examples
mysqlemailemail-validation

MySQL Wildcard Match Substring for Two Fields

here's what I'd like do to:

I have a MYSQL database that has several fields - the fields I'm referring to are contact_email and site_url in a table called url_links

What I'm looking for is a simple 'update' query that will set a binary flag if there's a match.

in pseudo-code:

Update url_links SET domainMatch=1 where { substring of contact_email after @ } = { substring of site_url - could be http:// https:// https://www or any url - i.e. just the domain name }

so if email is something like [email protected] and domain is http://www.mywebsiteisgreat.com it will not match

but if the email is [email protected] it would set the domainMatch field to 1 - the domains match.

Thanks!

Solution

  • Try this query:

    UPDATE url_links
SET domainMatch = 1
WHERE SUBSTRING_INDEX(contact_email,'@', -1)
LIKE CONCAT('%', SUBSTRING_INDEX(site_url,'.', -2), '%');

    ( I updated this with the database actual field names so we can test it easier )