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)

Although

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).