CODEWITHSUNDEEP
mysqlemailemail-validation
Viktor
Viktor

Reputation: 547

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!

Upvotes: 0

Views: 199

Answers (1)

The One and Only ChemistryBlob
The One and Only ChemistryBlob

Reputation: 7884

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 )

Upvotes: 2

Related Questions