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

8820 ワード
TreasureData SQL 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でバリデーションすることはオススメの方法ではない。
見直すべきところは別にある。

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

参考

C/C++文字を分割して文字配列インスタンスを返す
C++新特性～lambda抜粋