刪除額外的電子郵件字元以強制唯一性

• Gmail 會忽略.電子郵件地址內部，例如abc@gmail.com並且a.b.c@gmail.com相同
• 如果帳戶+符號後有任何數字，Gmail 也會考慮相同的帳戶。例如xyz@gmail和xyz+1@gmail.com相同

我想限制電子郵件的唯一性，這些電子郵件可以在 postgres 中處理上述情況，但到目前為止還沒有成功。簡單唯一且以小寫形式保存不起作用

回答您的問題（+1 表示有趣、具有挑戰性和相關的問題）。我做了以下事情：

創建了一個表：

CREATE TABLE regexp_test (my_email_string VARCHAR(255));

用範例數據填充它（根據問題，+標誌可以出現在電子郵件地址中的唯一位置是在第一個（本地）部分的末尾，它和@標誌之間有 1 個或多個數字：

INSERT INTO regexp_test 
VALUES
('as.dfWEWd.fs+4@stuff.com'), 
('aS.Sdf.34343a.sfs@yahoo.com'),
('adsFFdsf"£"££$£$+15@tester.cn'),   <-- non-standard characters!
('test_dots.asdf.+12345@blah.au.com')  

-- multiple dots after the @ sign
-- this is the tricky part!  

然後執行這個查詢：

SELECT 
 LOWER
 (
   REGEXP_REPLACE
   (
     REPLACE
     (
       SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1)
       , '.', ''
     ) 
       || 
       '@' 
       || 
       SUBSTRING(my_email_string, '[^@]*$'), 
     '[^a-zA-Z0-9@.]', '', 'g'
   )
 ) AS "New email" 
FROM regexp_test;

查詢說明如下。

結果：

              New Email
   asdfwewdfs@stuff.com
assdf34343asfs@yahoo.com
    adsffdsf@tester.cn
testdotsasdf@blah.au.com

所有這些都可以在這裡找到。

TL;DR - 這將適用於在電子郵件的第一（本地）部分中包含多個句點（點或句點）的電子郵件（這些將根據問題被刪除）OR在第二個（域）部分中 - 這個部分可以合法地有多個點 - 比如 blah.au.com。任何不是字母數字的符號都將從本地部分中刪除。+a和@本地部分中的符號之間的任何內容也將被刪除。

特別是，像這樣的電子郵件john.m.doe+15@blah.au.com將被轉換為johnmdoe@blah.au.com.

要解釋查詢，最好從內到外（您可以在小提琴本身上看到一些邏輯進展）。

第一個最裡面的部分使用SPLIT_PART()兩次：

SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1) 

它將（chomps）電子郵件字元串直到第一個@符號（並且僅@在有效的電子郵件地址中籤名）。然後第二個SPLIT_STRING將字元串進一步切碎，並刪除從+符號到@符號的任何內容。

然後外

REPLACE
(
 SPLIT_PART(SPLIT_PART(my_email_string, '@', 1), '+', 1) 
 , '.', ''
) 

根據問題刪除（替換'.'為''- 無）字元串的第一個（本地）部分中的所有點，但不在第二個（域）部分中（地址等@blah.com.au有效）。

現在，我使用正則表達式通過 SUBSTRING() 函式獲取電子郵件域，如下所述：

SUBSTRING(my_email_string, '[^@]*$'), 

這為我們提供了從@符號之後到字元串末尾的域。

然後，我使用 SQL 字元串連接運算符（||- 雙管道）連接兩個字元串並重新插入已刪除的@符號。

所以，我的數據現在看起來像這樣（result_1）：

asdfWEWdfs@stuff.com
aSSdf34343asfs@yahoo.com
adsFFdsf"£"££$£$@tester.cn
test_dotsasdf@blah.au.com

因此，電子郵件字元串中剩下的唯一點和 at 符號是有效的，但我們仍然必須從字元串中刪除+符號（來自問題）和其他符號（用於檢查）。為此，我們使用REGEXP_REPLACE.

因此，查詢現在看起來像這樣（result_1為清楚起見，替換為）。

REGEXP_REPLACE(result_1, '[^a-zA-Z0-9@.]', '', 'g')

這將替換 az 或 AZ 中的所有（'g'對於全域）字元而不是 ( ^) 或 @ 符號或沒有任何內容的點 ( '')。

事實上，這部分可能是多餘的。如果在字元串的本地部分中除了 s（點）之外沒有符號，或者符號後跟數字然後是符號，那麼就不需要了！我把它扔了以防萬一！.``+``@``REGEXP_REPLACE

LOWER()只是整理一下 - 我更喜歡小寫的電子郵件 ！

你去吧！

引用自：https://dba.stackexchange.com/questions/208558