SQLite使用GROUP BY而不区分大小写

Question

我的表中有一列电子邮件地址，其中地址可能全部为小写、大写、camelcase等。我如何修改我的查询，使地址像[e-mail]和[e-mail]将被分组在一起。

这是我的实际问题：

SELECT count(Email) as Number, Email
FROM tbl_SAP_Users
GROUP BY Email
HAVING Number > 1
ORDER BY Number DESC;

Answer 1

您可以group by lower(Email):

SELECT count(*) as Number, lower(Email) Email
FROM tbl_SAP_Users
GROUP BY lower(Email)
HAVING Number > 1
ORDER BY Number DESC;

或者在GROUP BY子句中使用COLLATE NOCASE：

SELECT count(*) as Number, Email
FROM tbl_SAP_Users
GROUP BY Email COLLATE NOCASE
HAVING Number > 1
ORDER BY Number DESC;

Answer 2

您可以尝试LOWER((函数使它们都小写。

您可能还想使用TRIM((来处理两端的空白。

SELECT count(Email) as Number, Email
FROM tbl_SAP_Users
GROUP BY LOWER(TRIM(Email))
HAVING Number > 1
ORDER BY Number DESC;