1

I want to fetch email addresses from the table which ends with below domain names.

@yahoomail.com
@accelerator.com
@learning.com
@cloud.vintage.com
user739115
  • 1,117
  • 5
  • 20
  • 41
  • `where email like '%@yahoomail.com' or email like '%@accelerator.com' ...` – juergen d Oct 16 '20 at 06:35
  • Does this answer your question? [SQL SELECT WHERE string ends with Column](https://stackoverflow.com/questions/25413692/sql-select-where-string-ends-with-column) – Ali Torabi Oct 16 '20 at 06:38
  • 1
    Just as a word of warning: a search with an "ends with" condition, using `LIKE '%(somestring)'` is going to be very inefficient and very slow on a large table. And unfortunately, due to the condition with a leading `%`, no index can help speed that up .... – marc_s Oct 16 '20 at 06:42

2 Answers2

1
SELECT EMAIL_COLUMN_NAME
FROM TABLE_NAME
WHERE EMAIL_COLUMN_NAME LIKE '%@yahoomail.com' OR 
      EMAIL_COLUMN_NAME LIKE '%@accelerator.com' OR 
      EMAIL_COLUMN_NAME LIKE '%@learning.com' OR
      EMAIL_COLUMN_NAME LIKE '%@cloud.vintage.com'
Riley Eaton
  • 137
  • 12
0

rileyeaton_s answer is correct but I would like to add a recommendation. If your plan is to do this search multiple times and want to improve the performance, you can add a persisted computed column that contains only the domain.

In the following code I have added a computed column that extracts the domain (including @) and also adds an index on that column.

DECLARE @test TABLE (
    Email nvarchar(255),
    Domain AS SUBSTRING(Email, CHARINDEX('@', Email), 255) PERSISTED,
    INDEX IX_Domain (Domain))

INSERT INTO @test VALUES ('abc@123.com')

SELECT * FROM @test WHERE Domain = '@123.com'

This query will give the result:

Email        Domain
abc@123.com  @123.com
Kristofer
  • 675
  • 7
  • 15