extracting the domain from an email address

Поиск
Список
Период
Сортировка
От Mark Steben
Тема extracting the domain from an email address
Дата
Msg-id CADyzmyx0hx6W3WL9ew7D2i404M=R0DDOLgdv7MhdMVPB-0-OgA@mail.gmail.com
обсуждение исходный текст
Ответы Re: extracting the domain from an email address  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Re: extracting the domain from an email address  (Rui DeSousa <rui@crazybean.net>)
Список pgsql-admin
Good morning,
I am searching for a better more efficient way to extract the domain portion of an email address.  The two I have been using are very expensive.
   The one extract I mostly use:  substring(email from '@(.*)$')
       also has an index on the email column which is the full email address:
              btree (email DESC) WHERE email::text = "substring"(email::text, '@(.*)$'::text) AND length(email::text) > 0,
      The other extract:
      substr(e.email,(strpos(e.email, '@') + 1))
   Currently has no index.

The referenced table has 72 million rows.  The email column can be empty (ergo the length = 0 check.  I am running postgresql 9.4.

Any insights/observations welcome.
            
         
     

--
Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089

t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com





В списке pgsql-admin по дате отправления:

Предыдущее
От: Morris de Oryx
Дата:
Сообщение: Re: How to set default owner of objects in Postgresql
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: extracting the domain from an email address