0

I have a list of E-Mail addresses of varying domain name e.g. [email protected], [email protected], [email protected] 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