How to get the domain name without '.com'?

Question

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

Returns

gmail.com

Do I need to concat to get:

gmail

Answer 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 [email protected] .

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;