SQL case-sensitive-reprint

SQL case-sensitive-reprint

①

You do not have to install it to differentiateCase sensitivity, Directly convert the value to be comparedBinaryAffect performance. ② add rules, not supported by sql7
Compare the following two items:
Print cast ('A'AsBinary)

Print cast ('A'AsBinary)

 

②

 

The database is not set to case sensitive when you install the database. To write SQL statements, use case sensitive. The following method is used. If you have other methods, please advise.

Case Sensitive: Select * from table where Col = 'semye' collate chinese_prc_cs_ai

Case-insensitive: Select * from table where Col = 'semye' collate chinese_prc_ci_ai

In order to facilitate your learning and understanding, I have reproduced the following:Article:

 

In create tableSuch a statement, for example:Password nvarchar (10) Collate chinese_prc_ci_as nullSo what does it mean? Take a look at the following:

First,CollateIs a clause that can be applied to database definitions or column definitions to define sorting rules, or to string expressions to apply projection of sorting rules.

Syntax:Collate collation_name

Collation_name
::= {Windows_collation_name} | {SQL _collation_name}

ParametersCollate_nameIs the name of the sorting rule applied to expressions, column definitions, or database definitions.Collation_nameYou can only specifyWindows_collation_nameOrSQL _collation_name.

Windows_collation_nameYesWindowsThe name of the sorting rule. SeeWindowsThe name of the sorting rule.
SQL _collation_nameYesSQLThe name of the sorting rule. SeeSQLThe name of the sorting rule.

The following is a brief introduction.Sorting rules:

What is a sorting rule?MSIt is described as follows:"InMicrosoft SQL Server 2000Medium,
The physical storage of strings is controlled by sorting rules. Sorting rules specify the bit mode and storage of each character
Rules Used for saving and comparing characters."
Run the following statement in the query analyzer to obtainSQLServerAll supported sorting rules.

Select * From: fn_helpcollations ()

The name of a sorting rule consists of two parts. The first half is the character set supported by this sorting rule.
For example:
Chinese_prc_cs_ai_ws
First half: refers Unicode Character Set, Chinese_prc _ Pointer to simplified Chinese characters Unicode .
The second half of the sorting rule is the suffix. Meaning:
_ Bin Binary sorting
_ Ci (CS) Case Sensitive, Ci Not differentiated, CS Differentiation
_ AI () Whether to differentiate accent, AI Not differentiated, As Differentiation
_ KI (KS) Whether to distinguish the Kana type , Ki Not differentiated, KS Differentiation
_ Wi (WS) Whether to differentiate width Wi Not differentiated, WS Differentiation

Case Sensitive : Select this option if you want to compare uppercase and lowercase letters.
Distinguish Stress : Select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected,
Comparison also treats letters with different accents as unequal.
Distinguish Kana : If you want to compare Katakana and katakana Japanese syllables, select this option.
Differentiated width : Select this option if you want to compare the half-width and full-width characters.