[SQL][Treasure Data][Presto] メールアドレスのバリデーションをSQLでかける。

そもそも

Invalid e-mail addressエラーという悲しいエラーがMarketoで吐かれている。
つまり、私たちは、打ち間違え諸々に対して、正規表現を用いて

• 省いたり
• すげかえたり

してあげねばならない。

というわけで

わかりやすさのために、除外すべきものを抽出する形でクエリを書いていく。

前処理

テーブルの想定は、

• contacts TBL
  • id: PK
  • type_id: type of contact information
  • contact: telno, email, etc

with
  emails as(
    select
      contacts.id as id
      , regexp_replace(contacts.contact,' |　|\s|\t','') as contact
    from
      contacts
    where
      contasts.type_id = 5 -- email
  )

空白系を片っ端から消しておく。

入門編 - like -

まぁ、難しいこともできるが、たいがいのやつはこいつで省ける。

select
  *
from
  emails
where
  not emails.contact LIKE '%_@__%.__%'

• @前に文字がある
• @入ってる
• @の後に文字がある
  • そんで . がある
  • .で終わってない

入門編 - regexp_like -

Treasure Dataはなんてったって、この関数がよい。
like も悪くはないんだけど、もう少しカスタマイズしたくなることあるよね。

select
    *
from
  emails
where
  not regexp_like( emails.contact, '.+@.+\..+')

要件は、likeと同じ。

確認編 - full outer join -

どちらも同じレコードが抽出できていることを確認しよう。

with
  emails as(
    select
      contacts.id as id
      , regexp_replace(contacts.contact,' |　|\s|\t','') as contact
    from
      contacts
    where
      contasts.type_id = 5 -- email
  )
  , cte_like as (
    select
      emails.id as id
      , emails.contact as contact
    from
      emails
    where
      not emails.contact LIKE '%_@__%.__%'
  )
  , cte_regexp as (
    select
      emails.id as id
      , emails.contact as contact
    from
      emails
    where
      not regexp_like( emails.contact, '.+@.+\..+')
  )

select
  cte_like.contact
  , cte_regexp.*
from
  cte_like
  full outer join
    cte_regexp
    on cte_like.id = cte_regexp.id
where
  cte_like.id is null
  or cte_regexp.id is null

人生初のfull outer joinに挑戦だ。
full outer joinなんてもののありがたみをここで初めて知るだろう。

中級編 - ちょっと分かる人用 -

はい、かっこよくできました。

select
  emails.id as id
  , emails.contact as contact
from
  emails
where
  not regexp_like(emails.contact, '^[a-zA-Z0-9.!#$%&*+\/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)+$')

まとめ

入門編で十分いける。
そして、そもそもSQLでバリデーションすることはオススメの方法ではない。
見直すべきところは別にある。

そして、上記があっているかどうか、保証はできない。
少なくとも、私の環境で目検はした、くらいのもの。

参考

• stack overfolw - Sql script to find invalid email addresses
• Email Address Regular Expression That 99.99% Works. Disagree?
  • ほぼ参考にしたけど、一部、
    • SQLで動きやすいよう
    • 最後が . で終わらないよう
  • ちょっとだけ書き換えた