如何在SQL Server中的所有大写字母中查找值？

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)

9 个解决方案

#1

You can force case sensitive collation;

您可以强制区分大小写的排序规则;

select * from T
  where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS

#2

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）重建master数据库，在setup命令的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）导入所有数据。

#3

Be default, SQL comparisons are case-insensitive.

默认情况下，SQL比较不区分大小写。

#4

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

#5

Could you try using this as your where clause?

您可以尝试将此作为where子句吗？

WHERE PATINDEX(FirstName + '%',UPPER(FirstName)) = 1

#6

Have a look here

看看这里

Seems you have a few options

似乎你有几个选择

cast the string to VARBINARY(length)

将字符串转换为VARBINARY（长度）
use COLLATE to specify a case-sensitive collation

使用COLLATE指定区分大小写的排序规则
calculate the BINARY_CHECKSUM() of the strings to compare

计算要比较的字符串的BINARY_CHECKSUM（）
change the table column’s COLLATION property

更改表列的COLLATION属性
use computed columns (implicit calculation of VARBINARY)

使用计算列（隐式计算VARBINARY）

#7

Try This

尝试这个

SELECT *
FROM MyTable
WHERE UPPER(FirstName) COLLATE Latin1_General_CS_AS = FirstName COLLATE Latin1_General_CS_AS

#8

You can find good example in Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server

您可以在区分大小写搜索中找到很好的示例：在SQL Server上获取小写或大写字符串

#9

I created a simple UDF for 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 WHERE clause.

然后，您可以在WHERE子句中的任何列上轻松使用它。

To use the OP example:

要使用OP示例：

SELECT *
FROM   MyTable
WHERE  dbo.fnIsStringAllUppercase(FirstName) = 1

#1