MySQL 如何查找值包含小写字母的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4123418/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find rows that have a value that contains a lowercase letter
提问by ripper234
I'm looking for an SQL query that gives me all rows where ColumnX contains any lowercase letter (e.g. "1234aaaa5789"). Same for uppercase.
我正在寻找一个 SQL 查询，它为我提供 ColumnX 包含任何小写字母的所有行（例如“1234aaaa5789”）。大写也一样。
回答by geon
SELECT * FROM my_table
WHERE UPPER(some_field) != some_field
This should work with funny characters like ????ü??. You might need to use a language-specific utf-8 collation for the table.
这应该适用于像 ????ü?? 这样的有趣角色。您可能需要对表使用特定于语言的 utf-8 排序规则。
回答by Devraj Gadhavi
SELECT * FROM my_table WHERE my_column = 'my string'
COLLATE Latin1_General_CS_AS
This would make a case sensitive search.
这将进行区分大小写的搜索。
EDIT
编辑
As stated in kouton's comment hereand tormuto's comment herewhosoever faces problem with the below collation
正如指出kouton的在这里评论和tormuto的在这里评论凡面临着以下问题整理
COLLATE Latin1_General_CS_AS
should first check the default collationfor their SQL server, their respective database and the column in question; and pass in the default collation with the query expression. List of collations can be found here.
应该首先检查他们的 SQL 服务器、他们各自的数据库和有问题的列的默认排序规则；并传入带有查询表达式的默认排序规则。可以在此处找到排序规则列表。
回答by NBS
SELECT * FROM Yourtable
WHERE UPPER([column_NAME]) COLLATE Latin1_General_CS_AS !=[Column_NAME]
回答by El David
for search all rows in lowercase
以小写形式搜索所有行
SELECT *
FROM Test
WHERE col1
LIKE '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
Thanks Manesh Joseph
回答by martincarlin87
This is how I did it for utf8 encoded table and utf8_unicode_ci column, which doesn't seem to have been posted exactly:
这就是我为 utf8 编码表和 utf8_unicode_ci 列所做的，这似乎没有完全发布：
SELECT *
FROM table
WHERE UPPER(column) != BINARY(column)
回答by subhash
IN MS SQL server use the COLLATE clause.
在 MS SQL 服务器中使用 COLLATE 子句。
SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'
Adding COLLATE Latin1_General_CS_ASmakes the search case sensitive.
添加COLLATE Latin1_General_CS_AS使搜索区分大小写。
Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_ASis not case sensitive.
SQL Server 安装的默认排序规则SQL_Latin1_General_CP1_CI_AS不区分大小写。
To change the collation of the any column for any table permanently run following query.
要更改任何表的任何列的排序规则，请永久运行以下查询。
ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS
To know the collation of the column for any table run following Stored Procedure.
要了解在存储过程之后运行的任何表的列的排序规则。
EXEC sp_help DatabaseName
Source : SQL SERVER – Collate – Case Sensitive SQL Query Search
回答by Mahib
I've done something like this to find out the lower cases.
我已经做了这样的事情来找出小写。
SELECT *
FROM YourTable
where BINARY_CHECKSUM(lower(ColumnName)) = BINARY_CHECKSUM(ColumnName)
回答by Elzo Valugi
mysql> SELECT '1234aaaa578' REGEXP '^[a-z]';
回答by Jose Carlos Ramos Carmenates
I have to add BINARY to the ColumnX, to get result as case sensitive
我必须将 BINARY 添加到ColumnX，以获得区分大小写的结果
SELECT * FROM MyTable WHERE BINARY(ColumnX) REGEXP '^[a-z]';
回答by CJM
I'm not an expert on MySQL I would suggest you look at REGEXP.
我不是 MySQL 的专家，我建议您查看REGEXP.
SELECT * FROM MyTable WHERE ColumnX REGEXP '^[a-z]';
