SQL Server String Difference case method

Default in SQL Server is not sensitive to case, for example, Username= ' Jesse ' and Username= ' Jesse ' result are the same. When validating a password, you may need to be sensitive to string capitalization, and you need to do some processing to introduce two methods:

Method Ⅰ: Convert to binary and then compare, because the case of the ASC code is different. For example:

SELECT *

From T_user

Where CAST (field as varbinary) = cast (' Admin ' as varbinary)

Method Ⅱ: The use of sorting rules is also based on binary. Add collate chinese_prc_cs_as_ws after the field

Such as:

SELECT *

From T_user

where username= ' admin ' and PASSWORD collate chinese_prc_cs_as_ws = ' admin '

Whether case sensitivity is related to collation, the parts of the collation have the following meanings:

Example Analysis Chinese_prc_cs_ai_ws

First half: Refers to the Unicode character set, the collation of the CHINESE_PRC_ pointer to the Chinese simplified characters Unicode.

The latter part of the collation is the suffix meaning:

_bin binary Sorting

_ci (CS) is case-sensitive, CI does not differentiate, CS differentiates

_ai (AS) is accent-sensitive, AI does not differentiate, as differentiates

Whether _ki (KS) differentiates kana types, KI does not differentiate, KS differentiates

Whether _wi (WS) differentiates width WI does not differentiate, WS differentiates

Case sensitive: Select this option if you want comparisons to treat uppercase and lowercase letters as unequal.

Accent Sensitivity: Select this option if you want comparisons to treat accent and unstressed letters as unequal. If you select this option, the comparison also treats letters with different accents as unequal.

Kana: Select this option if you want comparisons to treat katakana and hiragana Japanese syllables as unequal.

Width sensitive: If you want the comparison to treat Half-width and full-width characters as unequal, select this option