I am not really sure how you can even phrase this because it sounds strange conceptually, but I'll try it out. Essentially I am searching for a method to produce a query that's basically a Whereby LIKE Choose statement.
For example, basically desired to find all user records having a hotmail.com current email address, I possibly could make a move like:
SELECT UserEmail FROM Users WHERE (UserEmail LIKE '%hotmail.com')
But let's say I needed to utilize a subquery because the matching criteria? Something similar to this:
SELECT UserEmail FROM Users WHERE (UserEmail LIKE (SELECT '%'+ Domain FROM Domains))
Is the fact that even possible? If that's the case, what is the right syntax?
You can test
SELECT u.* FROM Users u INNER JOIN Domains d ON u.UserEmail LIKE '%'+ d.Domain
As well as try
SELECT u.* FROM Users u WHERE EXISTS(SELECT 1 FROM Domains d WHERE u.UserEmail LIKE '%' + d.Domain)
SELECT u.UserMail FROM Users u WHERE EXISTS(SELECT 1 FROM Domains d WHERE u.UserEmail LIKE '%' + d.Domain)
can give that which you search for, please understand that they like is costly which you can shed a little of your time with (mysql dialect):
SELECT u.UserMail FROM Users u WHERE SUBSTRING_INDEX(u.UserMail, '@', -1) IN (SELECT d.Domain FROM Domains)
as well as
SELECT u.UserMail FROM Users u INNER JOIN Domains d ON SUBSTRING_INDEX(u.UserMail, '@', -1) = d.Domain
(which you can split e-mail into username and domain fields if this sounds like a typical operation inside your database)
EDIT: I skipped the MS SQL server tag. For your dialect
substring(UserMail, charindex('@', UserMail) + 1, len(UserMail) - charindex('@', UserMail) )
should outshine LIKE (because it will likely be carried out once per row in Customers and also the you're able to straight join, in which the like approach must be carried out for every value in Customers on each row in Domain names).
P.S. check my formulas for start and length in substring (it had been Friday evening yesterday).