SQL Server database query case-sensitive and full-width -- Application of sorting rules

For accidental reasons, when the inner join table is required, the corresponding field must beCase Sensitive. By default, SQL Server databases are case-insensitive. How can this problem be solved?

To achieve this, there must be at least three operation levels:

1.Database level: The entire databaseChar,Varchar,Text,Nchar,NvarcharAndNtextThe data is case sensitive. (For ease of description, the data types are not explicitly described below .) But there is an obvious disadvantage in doing so, that is, the comparison of these fields in the entire database must be strictly matched. For example, the following two SQL statements have two different results:

SELECT * FROM [TABLE] WHERE [COLUMN] LIKE ‘%KeyWord%’SELECT * FROM [TABLE] WHERE [COLUMN] LIKE ‘%keyword%’

Because databases are differentiated strictly by case when compared, this will make it difficult for us to meet very complex query requirements during program development, A particularly prominent example is keyword search-we cannot predict the case sensitivity of the customer's input content, and we cannot require the customer to input keywords to be case sensitive.

2.Table field level: Only case sensitive to specific table fields. In this way, the entire range of case sensitivity is reduced, from the whole database to the currently specified field. However, it still has some limitations.

3.Query level: This query is case sensitive only. This is also the implementation method highlighted in this article. In this way, we will not make any changes to the previous database. We will only make case-sensitive queries for the current query, and will not affect the query of these fields elsewhere in the program.

Well, we have mentioned three implementation levels. How can we implement them? This requires "sorting rules" in the SQL database. You can set sorting rules in database attributes or in field attributes of table design. What sort rules should be set? Next, let's talk about the sort rules at the query level. The following SQL snippet demonstrates the application of case-sensitive sorting rules at the query level:

Use tempdbset nocount on -- 1 -- print 'create table customer (ID int, uname varchar (10) insert into customer select 1, 'Jim 'Union allselect 2, 'simith 'Union allselect 3, 'uonun' select * from customer -- 2 -- print 'create an initial data table info' create table Info (uname varchar (10), phone varchar (11 )) insert into info select 'Jim ', '000000' Union allselect 'simith', '000000' Union allselect 'uonun ', '200' select * from info -- 3 -- print' is case-insensitive and case-insensitive. 'select C. ID, C. uname as 'C. uname', I. uname as 'I. uname', I. phonefrom customer as C inner join info as ion C. uname = I. uname -- 4 -- print 'case sensitive, not all halfwidth 'select C. ID, C. uname as 'C. uname', I. uname as 'I. uname', I. phonefrom customer as C inner join info as ion C. uname = I. unamecollate chinese_prc_cs_as -- 5 -- print 'case insensitive, full halfwidth 'select C. ID, C. uname as 'C. uname', I. uname as 'I. uname', I. phonefrom customer as C inner join info as ion C. uname = I. unamecollate chinese_prc_ci_ai_ws -- 6 -- print 'case sensitive, full halfwidth 'select C. ID, C. uname as 'C. uname', I. uname as 'I. uname', I. phonefrom customer as C inner join info as ion C. uname = I. unamecollate custom table customerdrop table info/* create an initial data table customerid uname -------------- 1 jim2 simith3 uonun create an initial data table infouname phone -------- ----------- Jim 13800000000 simith 13911111111 uonun 13812345678 is case insensitive, id C. uname I. uname phone ----------- ---------- ----------- 1 Jim 138000000002 simith 139111111113 uonun 13812345678 case sensitive, not all-round id c. uname I. uname phone ----------- ---------- ----------- 2 simith 139111111113 uonun 13812345678 is case-insensitive and fully-half-width id c. uname I. uname phone ----------- ---------- ----------- 1 Jim 138000000003 uonun 13812345678 case sensitive, full-width id c. uname I. uname phone ----------- ---------- ----------- 3 uonun 13812345678 */

From the preceding SQL statement, we can see that we use the Collate clause during the query, and the specified sorting rule can affect the query results. As shown in the figure above, this sorting rule can be used to distinguish between uppercase and lowercase letters, accent, Kana, and halfwidth.

Conclusion:

1. This article does not further study other applications of sorting rules, such as the impact on order.
2. This article does not further study the impact mechanism on multiple different fields during query.
3. For sample code, see coreplex code library: http://udnz.com/Code-409.htm

Refer to SQL Server 2008 books online:

Use the sorting rules: MS-help: // Ms. sqlcc. V10/ms. sqlsvr. v10.zh-CHS/s10de_1devconc/html/61cdbb6b-3ca1-4d73-938b-22e4f06f75ea.htm

Collate: MS-help: // Ms. sqlcc. V10/ms. sqlsvr. v10.zh-CHS/s10de_6tsql/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm