blocks|key|3382455|text|可以强制使用区分大小写的排序规则；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3382456|select+*+from+T
++where+fld+=+upper(fld)+collate+SQL_Latin1_General_CP1_CS_AS|code-block|syntax|javascript|3382457|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can force case sensitive collation;

<pre><code>select * from T
 where fld = upper(fld) collate SQL_Latin1_General_CP1_CS_AS
</code></pre>

blocks|key|923257|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|923258|+SELECT+*
++FROM+MyTable
+WHERE+FirstName+=+UPPER(FirstName)+COLLATE+SQL_Latin1_General_CP1_CS_AS|code-block|syntax|javascript|923259|此排序规则允许区分大小写的比较。|923260|如果要更改数据库的排序规则，以便不需要在查询中指定区分大小写的排序规则，则需要执行以下操作(from+MSDN)：|offset|length|923261|1)确保您拥有重新创建用户数据库和其中的所有对象所需的所有信息或脚本。|923262|2)使用bcp+Utility等工具导出所有数据。|923263|3)删除所有用户数据库。|923264|4)重建master数据库，在setup命令的SQLCOLLATION属性中指定新的排序规则。例如：|923265|Setup+/QUIET+/ACTION=REBUILDDATABASE+/INSTANCENAME=InstanceName+
/SQLSYSADMINACCOUNTS=accounts+/[+SAPWD=+StrongPassword+]+
/SQLCOLLATION=CollationName|923266|5)创建所有数据库和其中的所有对象。|923267|6)导入所有数据。|923268|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms179254.aspx^0|0|0|0|1A|9|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1A|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1B|8|@]|9|@$K|1C|L|1D|1|1E]]|A|$]]|$1|M|3|N|5|6|7|1F|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17]]]]

Try

<pre><code> SELECT *
 FROM MyTable
 WHERE FirstName = UPPER(FirstName) COLLATE SQL_Latin1_General_CP1_CS_AS
</code></pre>

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 (<a href="http://msdn.microsoft.com/en-us/library/ms179254.aspx" rel="noreferrer">from MSDN</a>):

1) Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

2) Export all your data using a tool such as the bcp Utility. 

3) Drop all the user databases.

4) Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

<pre><code>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
/SQLCOLLATION=CollationName
</code></pre>

5) Create all the databases and all the objects in them.

6) Import all your data.

blocks|key|3382541|text|您需要使用区分大小写的服务器排序规则，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3382542|SELECT+*+
FROM+MyTable
WHERE+FirstName+=+UPPER(FirstName)+Collate+SQL_Latin1_General_CP1_CS_AS|code-block|syntax|javascript|3382543|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You need to use a server collation which is case sensitive like so:

<pre><code>SELECT * 
FROM MyTable
WHERE FirstName = UPPER(FirstName) Collate SQL_Latin1_General_CP1_CS_AS
</code></pre>

blocks|key|923170|text|默认情况下，SQL比较不区分大小写。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|923171|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Be default, SQL comparisons are case-insensitive.

blocks|key|5280879|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5280880|SELECT+*
++FROM+MyTable
+WHERE+FirstName+=+LOWER(FirstName)|code-block|syntax|javascript|5280881|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try

<pre><code>SELECT *
 FROM MyTable
 WHERE FirstName = LOWER(FirstName)
</code></pre>

blocks|key|5280617|text|你能试着用这个作为你的where子句吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5280618|WHERE+PATINDEX(FirstName+%2B+'%25',UPPER(FirstName))+=+1|code-block|syntax|javascript|5280619|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Could you try using this as your where clause?

<pre><code>WHERE PATINDEX(FirstName + '%',UPPER(FirstName)) = 1
</code></pre>

blocks|key|4996032|text|看一看here|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4996033|看起来你有几个选择|4996034|将字符串强制转换为VARBINARY(length)|4996035|use+COLLATE以指定区分大小写的collation|unordered-list-item|4996036|
|4996037|calculate要比较的字符串的BINARY_CHECKSUM()+|4996038|4996039|更改表列的排序规则属性|4996040|4996041|使用计算列(VARBINARY的隐式计算)|4996042|4996043|entityMap|0|LINK|mutability|MUTABLE|url|http://devio.wordpress.com/2008/02/23/case-sensitive-comparison-in-sql-server/^0|3|4|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|D|3|E|5|6|7|17|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|18|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|19|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|C|$]]|$1|M|3|N|5|J|7|1B|8|@]|9|@]|C|$]]|$1|O|3|L|5|6|7|1C|8|@]|9|@]|C|$]]|$1|P|3|Q|5|J|7|1D|8|@]|9|@]|C|$]]|$1|R|3|L|5|6|7|1E|8|@]|9|@]|C|$]]|$1|S|3|T|5|J|7|1F|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1G|8|@]|9|@]|C|$]]|$1|V|3|-4|5|6|7|1H|8|@]|9|@]|C|$]]]|W|$X|$5|Y|Z|10|C|$11|12]]]]

Have a look <a href="http://devio.wordpress.com/2008/02/23/case-sensitive-comparison-in-sql-server/" rel="nofollow">here</a>

Seems you have a few options

<ul>
<li>cast the string to VARBINARY(length)</li>
<li>use COLLATE to specify a case-sensitive collation</li>
<li>calculate the BINARY_CHECKSUM() of the strings to compare</li>
<li>change the table column’s COLLATION property</li>
<li>use computed columns (implicit calculation of VARBINARY)</li>
</ul>

blocks|key|5280795|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5280796|SELECT+*
FROM+MyTable
WHERE+UPPER(FirstName)+COLLATE+Latin1_General_CS_AS+=+FirstName+COLLATE+Latin1_General_CS_AS|code-block|syntax|javascript|5280797|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try This

<pre><code>SELECT *
FROM MyTable
WHERE UPPER(FirstName) COLLATE Latin1_General_CS_AS = FirstName COLLATE Latin1_General_CS_AS
</code></pre>

blocks|key|923282|text|您可以在Case+Sensitive+Search:+Fetching+lowercase+or+uppercase+string+on+SQL+Server中找到很好的示例|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|923283|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mytecbits.com/microsoft/sql-server/case-sensitive-search-fetching-lowercase-or-uppercase-string^0|4|23|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can find good example in <a href="http://www.mytecbits.com/microsoft/sql-server/case-sensitive-search-fetching-lowercase-or-uppercase-string" rel="nofollow">Case Sensitive Search: Fetching lowercase or uppercase string on SQL Server</a>

blocks|key|920385|text|为此，我创建了一个简单的UDF：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|920386|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|code-block|syntax|javascript|920387|然后，您可以很容易地在WHERE子句中的任何列上使用它。|920388|要使用OP示例：|920389|SELECT+*
FROM+++MyTable
WHERE++dbo.fnIsStringAllUppercase(FirstName)+=+1|920390|entityMap^0|C|3|0|0|B|5|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|R|$]]

I created a simple <code>UDF</code> for that:

<pre><code>create function dbo.fnIsStringAllUppercase(@input nvarchar(max)) returns bit

 as

begin

 if (ISNUMERIC(@input) = 0 AND RTRIM(LTRIM(@input)) &gt; '' AND @input = UPPER(@input COLLATE Latin1_General_CS_AS))
 return 1;

 return 0;
end
</code></pre>

Then you can easily use it on any column in the <code>WHERE</code> clause.

To use the OP example:

<pre><code>SELECT *
FROM MyTable
WHERE dbo.fnIsStringAllUppercase(FirstName) = 1
</code></pre>

blocks|key|4996232|text|回答这个问题的简单方法是使用排序规则。让我尝试解释一下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4996233|SELECT+*
++FROM+MyTable
+WHERE+FirstName+COLLATE+SQL_Latin1_General_CP1_CI_AS='SMITH’|code-block|syntax|javascript|4996234|在上面的查询中，我使用了collate，而没有使用任何内置的sql函数，比如‘UPPER’。原因是使用内置函数有其自身的影响。|4996235|请找到链接以更好地了解：|4996236|performance+impact+of+upper+and+collate|offset|length|4996237|entityMap|0|LINK|mutability|MUTABLE|url|https://www.mssqltips.com/sqlservertip/3215/how-column-collation-can-affect-sql-server-query-performance/^0|0|0|0|0|0|13|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Simple way to answer this question is to use collation. Let me try to explain:
<pre><code>SELECT *
 FROM MyTable
 WHERE FirstName COLLATE SQL_Latin1_General_CP1_CI_AS='SMITH’
</code></pre>
In the above query I have used collate and didn’t use any in built sql functions like ‘UPPER’. Reason because using inbuilt functions has it’s own impact.
Please find the link to understand better:
<a href="https://www.mssqltips.com/sqlservertip/3215/how-column-collation-can-affect-sql-server-query-performance/" rel="nofollow noreferrer">performance impact of upper and collate</a>

How can I find column values that are in all caps? Like <code>LastName = 'SMITH'</code> instead of <code>'Smith'</code>

Here is what I was trying...

<pre><code>SELECT *
 FROM MyTable
 WHERE FirstName = UPPER(FirstName)
</code></pre>

How to find values in all caps in SQL Server?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何查找全部大写的列值？像LastName = 'SMITH'而不是'Smith'这就是我所尝试的..。SELECT *  FROM MyTable WHERE FirstName = UPPER(FirstName)

问如何在SQL Server中查找全部大写的值？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中查找全部大写的值？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL Server中查找全部大写的值？
EN