0

I have a list of E-Mail addresses of varying domain name e.g. abc123@gmail.com, def456@woodstock.br, ghi789@jetbrains.net and was wondering how to get just the domain name (e.g. gmail, woodstock, jetbrains) from the E-Mails. So far I've gotten:

SELECT SUBSTR(Email, INSTR(Email, '@') + 1) as DOMAIN FROM Table

Which removes everything in front of and the '@' sign. I've seen a similar post for MySQL but as SQLite doesn't have the SUBSTRING_INDEX function I was wondering what alternative there were for SQLite

Any help would be great appreciated :)

Setsuna
  • 9
  • 1
  • 3

1 Answers1

2

You can use this its working: select substr(Email, instr(Email, '@') + 1) as DOMAIN from Table;

Reference Implementation:

http://sqliteonline.com/#fiddle-57f6dd1371fedc4893affeede56d1ad5d910a3d1a0ab612286

Update:

To Get Only Domain Provider name:

select replace(substr(name, instr(name, '@') + 1), ltrim(substr(name, instr(name, '@') + 1), replace(substr(name, instr(name, '@') + 1), '.', '')), '') as provider from demo;

Reference Implementation:

http://sqliteonline.com/#fiddle-57f6e07c3b960bc86eb66cd46c8958b6bdd1c34d4bb6456091

Shrijan Tiwari
  • 673
  • 6
  • 17
  • Hey, thanks for the response! I'm trying to get just the domain provider's name (e.g. gmail from abc123@gmail.com), any ideas how I could go about doing that? – Setsuna Oct 06 '16 at 23:29
  • I have updated above SQL according to your need, might work in your case. – Shrijan Tiwari Oct 06 '16 at 23:50