1

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*"')
halfer
  • 19,824
  • 17
  • 99
  • 186
user1777929
  • 777
  • 1
  • 10
  • 28

1 Answers1

3

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%'
MJH
  • 1,710
  • 1
  • 9
  • 19