1
SELECT substr(Emails, instr(Emails, '@')+1)
      FROM EmployeeEmails;

Returns

gmail.com

Do I need to concat to get:

gmail
Lizzie
  • 179
  • 2
  • 2
  • 12
  • 1
    Possible duplicate of [How to select domain name from email address](https://stackoverflow.com/questions/2628138/how-to-select-domain-name-from-email-address) – Balasubramanian Sep 18 '17 at 05:01
  • Email**s**??? Can the string contain multiple emails? – Thorsten Kettner Sep 18 '17 at 06:35
  • @ThorstenKettner Just one email. Each record has one email for example, bob@gmail.com, kristina@yahoo.com. I'm trying to return gmail, yahoo etc – Lizzie Sep 18 '17 at 06:49
  • Okay. So you chose a bad column name suggesting a value would represent multiple emails rather than one. It would be better to name this column `email` as each record only contains one email in this column. – Thorsten Kettner Sep 18 '17 at 06:54

4 Answers4

2

Check your relevant database query :

Query(for MySQL)

select (SUBSTRING_INDEX(SUBSTR(Emails, INSTR(Emails, '@') + 1),'.',1)) from EmployeeEmails;

Query(For Sql Server):

select SUBSTRING(Emails,(CHARINDEX('@',Emails)+1),1) from EmployeeEmails;

Query(For Oracle)

    select substr(Emails,instr(Emails,'@',1)+1) as domain
from EmployeeEmails;
Puja
  • 451
  • 2
  • 5
  • 20
  • This is the exact answer given in the duplicate question. – Tot Zam Sep 18 '17 at 05:07
  • Thanks for the response. I am getting an error: ORA-00904: "SUBSTRING_INDEX": Invalid identifier. – Lizzie Sep 18 '17 at 05:42
  • Thank you for editing the answer! I used this: select substr(Emails, INSTR(Emails,'@',1) + 1, 5) from EmployeeEmails to get the full domain. With 1,1 I just got the first initial 'g'. Is there a query that identifies when there is the .com? – Lizzie Sep 18 '17 at 06:12
  • @Lizzie, Check again my edited answer. I hope its helps to you – Puja Sep 18 '17 at 06:18
  • @Angel Thank you for the quick response! The Query(For Oracle) returns with the SUBSTRING_INDEX: invalid identifier. Should I change it to LENGTH(SUBSTR(Emails, '.', -1))))) – Lizzie Sep 18 '17 at 06:24
  • @Lizzie, Sorry I have no my oracle server, so I cant test it. What is the problem. – Puja Sep 18 '17 at 06:26
  • @Angel No worries. I'm getting an error ORA-00904: "SUBSTRING_INDEX": invalid identifier. – Lizzie Sep 18 '17 at 06:27
  • @Lizzie, Check my answer – Puja Sep 18 '17 at 06:32
  • @Angel Thank you for the update! Unfortunately, it returns "gmail.com". I am trying to get "gmail". I cannot move this discussion to chat because my reputation is too low. – Lizzie Sep 18 '17 at 06:34
  • Lizzie, yup Sorry, I cant help you more. Because have no oracle server so, I cant test them. Go with @Muhammad F. Musad and chanchal answer. – Puja Sep 18 '17 at 06:35
  • @Angel Thank you for all your help though! – Lizzie Sep 18 '17 at 06:36
1

You can use REGEXP_REPLACE to extract the domain name:

select regexp_replace(emails, '^[^@]+@([^.]+)\..+$', '\1') from employeeemails;

This works for any email of the pattern abcd@efgh.ijkl .

The pattern:

  • ^ start of the sting
  • [^@]+ 1 to n characters other than @
  • @ the at sign @
  • ( remember the following string
  • [^.]+ 1 to n characters other than the dot .
  • ) end of the string to remember
  • \. a dot .
  • .+ 1 to n characters
  • $ end of the string
  • \1 the remembered string

And here is the old-fashioned way without REGEXP_REPLACE:

select substr(emails,
              instr(emails, '@') + 1, 
              instr(emails, '.', instr(emails, '@') + 1) - instr(emails, '@') - 1
             )
from employeeemails;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

You can use LIKE with wild cards see here

  • underscore character ( _ ) for any single character.

  • percent sign character (%) for a string of zero or more characters.

    SELECT email FROM emails WHERE email NOT LIKE '%_@__%.__%'

This will ignore the following cases (simple version for valid emails):

  • emails that have at least one character before the @
  • emails that have at least two characters between @ and .
  • emails that have at least two characters between . and the end.
Wasif Ali
  • 886
  • 1
  • 13
  • 29
Muhammad F. Musad
  • 869
  • 8
  • 17
0

try this : -

declare @email sysname = 'testCode@gmail.com'
SELECT substring(@email, charindex('@',@email,0)+1 , (charindex('.',@email,0)-1 - charindex('@',@email,0)))
chanchal
  • 26
  • 4