sql - 查找区分大小写的记录

Question

我需要仅从表的一列中获取区分大小写的记录。

即，它们是 email_address 列，我必须从中找到相同的电子邮件地址，但情况不同。

例如:

email_ address
---------------
abc@gmail.com
hef@gmail.com
ABC@gmail.com
hrf@gmail.com

从上面给出的示例中。我需要一个查询来仅从 email_address 列中获取记录 abc@gmail.com 和 ABC@gmail.com .

Answer 1

我个人不喜欢这种方法，但这似乎是我现在唯一的解决方案，而且它也适用于混合情况:

select * from test t 
    where   
    email_address in 
    (
    select email_address from test group by email_address having count(email_address)> 1        
    )
    and left(email_address, charindex('@', email_address) -1 ) collate SQL_Latin1_General_CP1_CS_AS
        not in (select left(email_address, charindex('@', email_address) -1 ) from test 
                        where email_address collate SQL_Latin1_General_CP1_CS_AS <> t.email_address )

p/s:您需要将“test”替换为您的实际表名称