sql server 默认安装时(SQL_Latin1_General_CP1_CI_AS )是不区分大小写的,可以在安装时选择排序规则时使用SQL_Latin1_General_CP1_CS_AS ,如果系统已经安装好了,又不想修改数
据库,仅想在查询时是对大小写敏感可以这样写
select * from Production.Productwhere name='Abc' COLLATE SQL_Latin1_General_CP1_CS_AS
在视图里
CREATE VIEW
Production.ProductSensitive
AS
SELECT
ProductID,
Name,
Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive
FROM Production.Product;
/************************************************************************************************************/
The following code creates a view that returns the ProductID column, and two versions of the Name column from the AdventureWorks.Production.Product table. The first Name column is left to the same collation as the source column, but the second Name column
(aliased as NameSensitive) specifies a case sensitive collation for the column in the view.
USE AdventureWorks;
GO
CREATE VIEW
Production.ProductSensitive
AS
SELECT
ProductID,
Name,
Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive
FROM Production.Product;
The key piece is the line:
Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive
Most database developers know collations can be applied to databases and columns, but many don’t realize that we can apply collations to expressions as well. That is exactly what we are doing in that line. We are applying the case sensitive collation SQL_Latin1_General_CP1_CS_AS
to the expression in the select list.
After running the code above to make the view, we can test that it works correctly by running the following statements:
--Should match at least one row. The [Name] column
--is not case sensitive
SELECT * FROM Production.ProductSensitive
WHERE Name='awc logo cap';
--Won't match any rows because [NameSensitive] is
--case sensitive
SELECT * FROM Production.ProductSensitive
WHERE NameSensitive='awc logo cap';
--Will match rows because [NameSensitive] is
--case sensitive, and the 'AWC Logo Cap' literal
--uses the proper case.
SELECT * FROM Production.ProductSensitive
WHERE NameSensitive='AWC Logo Cap';
分享到:
相关推荐
The SQL Server client tools must be installed on your desktop computer and the SQL Server relational database server must be installed on either your desktop computer or on a remote server with ...
Pro SQL Server Internals is a book for developers and database administrators, and it covers multiple SQL Server versions starting with SQL Server 2005 and going all the way up to the recently ...
Pro SQL Server Internals is a book for developers and database administrators, and it covers multiple SQL Server versions starting with SQL Server 2005 and going all the way up to the recently ...
offering a fast-paced accessible tutorial to the major themes and techniques involved in applying the SQL language. Forta's examples are clear and his writing style is crisp and concise. As with ...
Developers and DBAs alike can benefit from the expressive power of Transact-SQL, and Pro T-SQL 2012 Programmer's Guide provides the gateway to success in applying this increasingly important database...
数控技术专业英语(第2版):Unit 12 Applying for a Job.ppt
Applying S88 batch control from a user's perspective
offering a fast-paced accessible tutorial to the major themes and techniques involved in applying the SQL language. Forta's examples are clear and his writing style is crisp and concise. As with ...
Applying Deep Learning To Answer Selection- A Study And An Open Task
offering a fast-paced accessible tutorial to the major themes and techniques involved in applying the SQL language. Forta's examples are clear and his writing style is crisp and concise. As with ...
Applying UML and Patterns1
This document provides an overview of Gartner AI-related research and analyst resources. Enterprise architecture and technology innovation leaders should leverage this reference to optimize Gartner ...
Deploy a working environment applying load balancing and fault tolerant solutions Create a collaborative space around the user’s needs, containing all the information and document history using the ...
NULL 博文链接:https://caohong286.iteye.com/blog/1577086
In Applying Design for Six Sigma to Software and Hardware Systems , two leading experts offer a realistic, step-by-step process for succeeding with DFSS. Their clear, start-to-finish roadmap is ...
UML和模式应用 Applying UML and Patterns(中文版).pdf
Applying UML and Patterns第三版电子书,完整版
LARMAN的关于软件工程知识方面的经典之作.书中用一个完整的POS项目,详细说明如何在大型软件开发项目中逐步建立OOP的概念,使用UML和PATTERN这些事半功倍的利器. 书是PDF 格式, 很好,很清晰.
Nick Harrison’s SQL Queries Succinctly will show you how to craft queries in SQL, from basic CRUD statements and slicing and dicing the data, to applying filters and using aggregate functions to ...