Art Art - 2 months ago 13
SQL Question

In Oracle Database, how do you extract text in-between characters? (SQL)

I'm new at SQL and I have been trying to practice working with Oracle Database.

While working on an activity, I came across one that confounds me -- I am trying to extract specific text from in-between characters. The specific activity that I cannot seem to figure out using these functions is:

For table called Users in the column called Emails, extract only the email domain (that is, the text in-between @ and .), so that full email addresses:


  • mjane@yahoo.com

  • jdoe@aol.com

  • jbarry@outlook.com



Becomes a new column called Domains with the values:


  • yahoo

  • aol

  • outlook



So far I have only been able to write code that isolates only the username to:


  • mjane

  • jdoe

  • jbarry



Using this code:

`select rtrim(Emails,substr(Emails,instr(Emails,'@')))
from StudentEmails;`


Any help would be extremely appreciated! I've been scratching my head about this for hours! Thank you!!!

Answer
SELECT SUBSTR(Emails,
              INSTR(Emails, '@') + 1,
              INSTR(Emails, '.') - INSTR(Emails, '@') - 1) AS Domains
FROM Users

Caveat:

I assume that emails have as single dot separating the domain name from domain type (e.g. com, net). If you had an email like joe@domain.his.us, then the above query would return domain as being the domain.