SQL Server String comparison Time Zone case-insensitive Method

By default, SQL Server is case insensitive. For example, username = 'jesse 'and username = 'jesse' have the same results. When verifying the password, you may need to be case sensitive to the string. You need to perform some processing to introduce two methods:

Method I: Convert to binary before comparison, because the case encoding is different. For example:

select   * from   T_User where   cast(field   as   varbinary)   =   cast( 'Admin'   as   varbinary) 

(Note: The nchar and char data types are differentiated. the encoding rules are different. The default encoding rules are non-Unicode)

Method II: sorting rules are also based on binary. Add collate chinese_prc_cs_as_ws to the field

For example:

select * from T_Userwhere userName='admin' AND PASSWORD collate Chinese_PRC_CS_AS_WS ='Admin'

Case sensitivity is related to sorting rules. The meaning of each part of the sorting rules is as follows:

Example: chinese_prc_cs_ai_ws

First half: the Unicode character set. The chinese_prc _ pointer sorts Unicode in simplified Chinese characters.
The second half of the sorting rule is the suffix meaning:
_ Bin binary sorting
_ Ci (CS) is case sensitive, CI is case insensitive, and CS is case sensitive
_ Whether AI (AS) distinguishes stress, AI does not distinguish,
_ KI (KS) indicates whether Kana is distinguished. Ki is not distinguished, and KS is distinguished.
_ Whether wi (WS) is differentiated by width WI and WS
Case Sensitive: select this option if you want to make the comparison between uppercase and lowercase letters different.
Accent differentiation: select this option if you want to treat the comparison as different from the accent and non-accent letters. If this option is selected, the comparison also treats the homophone with different accents as unequal.
Kana differentiation: select this option if you want to treat Katakana and katakana as different Japanese syllables.
Width differentiation: select this option if you want to make the comparison between halfwidth and fullwidth characters.