如何在 SQL Server 中查找所有大写的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8169792/
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 values in all caps in SQL Server?
提问by daveomcd
How can I find column values that are in all caps? Like LastName = 'SMITH'instead of 'Smith'
如何找到全部大写的列值？喜欢LastName = 'SMITH'而不是'Smith'
Here is what I was trying...
这是我正在尝试的...
SELECT *
FROM MyTable
WHERE FirstName = UPPER(FirstName)
回答by Alex K.
You can force case sensitive collation;
您可以强制区分大小写；
select * from T
where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS
回答by Joe Ratzer
Try
尝试
SELECT *
FROM MyTable
WHERE FirstName = UPPER(FirstName) COLLATE SQL_Latin1_General_CP1_CS_AS
This collation allows case sensitive comparisons.
此排序规则允许区分大小写的比较。
If you want to change the collation of your database so you don't need to specifiy a case-sensitive collation in your queries you need to do the following (from MSDN):
如果您想更改数据库的排序规则，以便不需要在查询中指定区分大小写的排序规则，您需要执行以下操作（来自 MSDN）：
1) Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
1) 确保您拥有重新创建用户数据库及其中所有对象所需的所有信息或脚本。
2) Export all your data using a tool such as the bcp Utility.
2) 使用 bcp Utility 等工具导出所有数据。
3) Drop all the user databases.
3) 删除所有用户数据库。
4) Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
4) 重建主数据库，在设置命令的 SQLCOLLATION 属性中指定新的排序规则。例如：
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName
5) Create all the databases and all the objects in them.
5) 创建所有数据库和其中的所有对象。
6) Import all your data.
6) 导入所有数据。
回答by Anja
You need to use a server collation which is case sensitive like so:
您需要使用区分大小写的服务器排序规则，如下所示：
SELECT *
FROM MyTable
WHERE FirstName = UPPER(FirstName) Collate SQL_Latin1_General_CP1_CS_AS
回答by drdwilcox
Be default, SQL comparisons are case-insensitive.
默认情况下，SQL 比较不区分大小写。
回答by Nonym
Could you try using this as your where clause?
您可以尝试将其用作 where 子句吗？
WHERE PATINDEX(FirstName + '%',UPPER(FirstName)) = 1
回答by Richard Friend
Have a look here
看看这里
Seems you have a few options
似乎你有几个选择
cast the string to VARBINARY(length)
use COLLATE to specify a case-sensitive collation
calculate the BINARY_CHECKSUM() of the strings to compare
change the table column's COLLATION property
use computed columns (implicit calculation of VARBINARY)
将字符串转换为 VARBINARY(length)
使用 COLLATE 指定区分大小写的排序规则
计算要比较的字符串的 BINARY_CHECKSUM()
更改表列的 COLLATION 属性
使用计算列（VARBINARY 的隐式计算）
回答by Sequenzia
Try This
尝试这个
SELECT *
FROM MyTable
WHERE UPPER(FirstName) COLLATE Latin1_General_CS_AS = FirstName COLLATE Latin1_General_CS_AS
回答by hkravitz
You can find good example in Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server
您可以在区分大小写的搜索中找到很好的示例：在 SQL Server 上获取小写或大写字符串
回答by isapir
I created a simple UDFfor that:
我UDF为此创建了一个简单的：
create function dbo.fnIsStringAllUppercase(@input nvarchar(max)) returns bit
as
begin
if (ISNUMERIC(@input) = 0 AND RTRIM(LTRIM(@input)) > '' AND @input = UPPER(@input COLLATE Latin1_General_CS_AS))
return 1;
return 0;
end
Then you can easily use it on any column in the WHEREclause.
然后您可以轻松地在WHERE子句中的任何列上使用它。
To use the OP example:
使用 OP 示例：
SELECT *
FROM MyTable
WHERE dbo.fnIsStringAllUppercase(FirstName) = 1
回答by Despedo
Try
尝试
SELECT *
FROM MyTable
WHERE FirstName = LOWER(FirstName)
