SQL Server 2014区分大小写问题 [英] SQL Server 2014 Case Sensitivity issue

问题描述

我正在将数据库和etl从MySQl迁移到SQL Server，并遇到了区分大小写的问题。
在MySql中，数据库设置为区分大小写，因为我们正在从中加载的应用程序之一在一组代码中具有 Divh和 divh之类的代码（不是我的工作）
都很好，并且etl中的select语句，查询报告等都使用了作者想要的关于大小写的任何内容-有些全都是UPPER，有些全是最低级。
因此，换句话说，MYSql具有不区分大小写的DDL和SQL，但允许区分大小写的数据。

I am migrating a database and etl from MySQl to SQL Server and have hit a case sensitivity issue. In MySql the DB is setup as case sensitive because one of the applications we are loading from has codes like 'Divh' and 'divh' in the one set (its not my doing) all is well and the select statements all over the place in etl, queries reports etc have all used whatever the author wanted regarding case - some are all UPPER some all lower most mixed. So, in other words MYSql has case-insensitive DDL and SQL but allows case sensitive data.

SQL Server似乎无法容纳此大小写。如果我选择CI归类，则所有表和列以及数据（大概）都不敏感。
与之相反-如果它的CS一切都区分大小写。
我读对了吗？
如果是这样，那么我要么必须更改数据库
中每个文本列的排序规则，要么编辑每个查询。

It doesn't look like SQL Server can accommodate this. If I choose a CI collation all the tables and columns are insensitive along with the data (presumably). and the converse - If its CS everything is case-sensitive. Am I reading it right ? If so then I either have to change the collation of every text column in the DB OR edit each and every query.

具有讽刺意味的是，第1个测试是针对使用相同排序规则（SQL_Latin1_General_CP1_CS_AS）
设置的Azure SQL数据库，它并不关心select中表名的大小写。

Ironically the 1st test was to an Azure SQL Database which was set up with the same collation (SQL_Latin1_General_CP1_CS_AS) and it doesn't care about the case of the table name in a select.

有什么想法吗？

谢谢
JC

Thanks JC

推荐答案

首先，您知道排序规则设置在SQL Server的每个级别上都存在吗？实例，数据库，表甚至字段级别。

Firstly are you aware that collation settings exist at every level in SQL Server; Instance, database, table and even field level.

听起来好像您只是想对受影响的字段强制执行区分大小写的排序规则，从而使数据库和DDL成为一个整体

It sounds like you just want to enforce the case sensitive collation for the affected fields leaving the database and DDL as a whole case insensitive.

我过去使用的另一个技巧是，如果您想在不同情况之间进行数据比较，则可以将值转换为VARBINARY数据类型。更改任何内容的排序规则。

Another trick i've used in the past is to cast values to a VARBINARY data type if you want to do data comparisions between different cases, but without the need to change the collation of anything.

例如：

DECLARE @Var1 VARCHAR(5) 
DECLARE @Var2 VARCHAR(5)

SET @Var1 = 'Divh' 
SET @Var2 = 'divh'

--Comparison1:
IF @Var1 = @Var2
    PRINT 'Same'
ELSE
    PRINT 'Not the same'

--Comparison2:
IF CAST(@Var1 AS VARBINARY) = CAST(@Var2 AS VARBINARY)
    PRINT 'Same'
ELSE
    PRINT 'Not the same'

这篇关于SQL Server 2014区分大小写问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！