在 MS 中强制 T-SQL 查询区分大小写

发布于 2024-09-26 05:46:06 字数 361 浏览 4 评论 0原文

我有一个源自旧遗留系统的表，该系统区分大小写，特别是状态列，其中“s”=“计划导入”和“S”=“计划管理”。该表最终进入 SQL Server 2000 数据库，我可以对其进行查询。我的查询相对简单，只是进行计数...

Select trans_type, count(1) from mytable group by trans_type

这将“S”的计数与“s”的计数分组。有没有办法强制查询对大小写敏感？我可以访问 SQL Server 2000 和 2005 环境来运行它，但是服务器上的管理能力有限（所以我无法设置服务器属性）...我想我可以将数据移动到本地并在在我的本地，我可以完全访问服务器选项，但更喜欢 tsql 解决方案。

原文

I have a table that originates in an old legacy system that was case senstive, in particular a status column where 's' = 'Schedule import' and 'S' = 'Schedule management'. This table eventually makes its way into a SQL Server 2000 database which I can query against. My query is relatively simple just going for counts...

Select trans_type, count(1) from mytable group by trans_type

This is grouping the counts for 'S' along with the 's' counts. Is there any way to force a query to be cap sensitive? I have access to both SQL Server 2000 and 2005 environments to run this, however have limited admin capability on the server (so I can't set server attributes)... I guess I could move the data to my local and setup something on my local where I have full access to server options, but would prefer a tsql solution.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

幻想少年梦 2024-10-03 05:46:06

select trans_type collate SQL_Latin1_General_CP1_CS_AS, count(*)
from mytable
group by trans_type collate SQL_Latin1_General_CP1_CS_AS

您也可以使用 =、like 和其他运算符来完成此操作。请注意，您必须修改选择列表，因为您不再按 trans_type 分组，而是按 trans_type collate SQL_Latin1_General_CP1_CS_AS 分组。有点陷阱。

select trans_type collate SQL_Latin1_General_CP1_CS_AS, count(*)
from mytable
group by trans_type collate SQL_Latin1_General_CP1_CS_AS

You can do this with =, like, and other operators as well. Note that you must modify the select list because you are no longer grouping by trans_type, you are now grouping by trans_type collate SQL_Latin1_General_CP1_CS_AS. Kind of a gotcha.

回复收藏 0 原文