当前位置: 动力学知识库 > 问答 > 编程问答 >

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:

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!!!

网友答案:
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 [email protected], then the above query would return domain as being the domain.

网友答案:

Regular expressions would be another way to go:

select substr(regexp_substr(emails,'@[^.]+'),2) domains from users;
分享给朋友:
您可能感兴趣的文章:
随机阅读: