How to pull up records which have only gmail, hotmail, yahoo email addresses in SQL Server 2014?

Question

I have a list of records in the database that have email addresses.

I would like to pull up records that have only @gmail, @Hotmail and @Yahoo email addresses. I also want the records sorted by domain name.

My code:

SELECT [AccountId]
      ,[FirstName]
      ,[LastName]
      ,[EMailAddress]
FROM [Contacts]

WHERE EMailAddress IN ('%@GMAIL%', '%@HOTMAIL%', '%@YAHOO%')

ORDER BY SUBSTRING(EMailAddress,(CHARINDEX('@',EMailAddress)+1),1)

The problem I am having is that I get ZERO records because I have no records where the email address is %@GMAIL or %@HOTMAIL or %@YAHOO.

My code would work if I changed it to:

WHERE EMailAddress LIKE '%@GMAIL%'

Then I would get all records with Gmail Addresses. But I want records with all of the above email addresses.

I also tried:

WHERE CONTAINS (EMailAddress, '"*GMAIL*" OR "*HOTMAIL*" OR "*YAHOO*"')

Answer 1

You cannot use wildcards in an IN clause like that, you have to use:

WHERE EmailAddress LIKE '%GMAIL%'
OR EmailAddress LIKE '%HOTMAIL%'
OR EmailAddress LIKE '%YAHOO%'