SQL Server String Difference case method

Source: Internet
Author: User
Tags character set comparison

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.