How to distinguish between uppercase and lowercase letters and halfwidth

Source: Internet
Author: User

Case Sensitive and full-width halfwidth (passed the SQL2000 test ):
/*
Use the defined sorting rules (chinese_prc_cs_as_ws, chinese_prc_bin) to differentiate,
Two sorting rules are introduced:
The first chinese_prc region (Chinese (Hong Kong Special Administrative Region), Chinese (People's Republic of China), Chinese (Singapore ))
CS (short for casesensiti.pdf): CS indicates case-sensitive, and CI indicates case-insensitive.
As (short for accentsensiti.pdf): As specifies the accent, while AI specifies the accent.
WS (widthsensiti.pdf): Ws specifies case sensitivity and omitted specifies case insensitive. (Differentiate the full-width and halfwidth)
Bin: Specify the binary sorting order.
Kanatypesensitive: Ks specifies the Kana type, and omitted specifies the case-insensitive.

_ Bin binary sorting
_ Ci_ai is case-insensitive, stress-insensitive, Kana-insensitive, and width-insensitive
_ Ci_ai_ws are case-insensitive, Kana-insensitive, and width-aware.
_ Ci_ai_ks is case-insensitive, case-insensitive, Kana-type, and width-insensitive.
_ Ci_ai_ks_ws are case-insensitive, ks_ws are Kana-type, and width-aware.
_ Ci_as is case-insensitive, stress-sensitive, but not Kana type, and width-insensitive
_ Ci_as_ws is case-insensitive, stress-sensitive, and Kana-type.
_ Ci_as_ks is case-insensitive, stress-sensitive, Kana-type, and width-insensitive.
_ Ci_as_ks_ws are case-insensitive, stress-sensitive, and ks_ws are case-insensitive.
_ Cs_ai case sensitive, accent-insensitive, Kana-insensitive, and width-insensitive
_ Cs_ai_ws case-sensitive, stress-insensitive, Kana-type, and width-sensitive
_ Cs_ai_ks case sensitive, accent-insensitive, Kana type, and width-insensitive
_ Cs_ai_ks_ws case-sensitive, case-insensitive, Kana-type, and width-sensitive
_ Cs_as case sensitive, stress-sensitive, non-Kana type, and width-insensitive
_ Cs_as_ws case sensitive, stress sensitive, not Kana type, width differentiated
_ Cs_as_ks case sensitive, stress sensitive, Kana type, width insensitive
_ Cs_as_ks_ws case sensitive, stress sensitive, Kana type, and width sensitive
*/
Create Table TA (ID int, name varchar (20 ))
Insert Ta
Select 1, 'A' Union all
Select 2, 'A' Union all
Select 3, 'a, 'Union all
Select 4, 'A ,'

Select * from Ta where name like 'a % '-- all records will be in the result set
Select * from Ta where name collate chinese_prc_cs_as_ws like '%, %' -- only 4th records meet
Select * from Ta where name collate chinese_prc_cs_as_ws like 'a % '-- 3rd records are satisfied (the result of adding _ WS is the same as that of the previous method)
Select * from Ta where name collate chinese_prc_cs_as_ws like 'a % '-- 2nd records meet
Select * from Ta where name collate chinese_prc_bin like 'A' -- 1st records are satisfied (same as binary sorting results)

The result is as follows:
ID name
-------------------------------
1
2
3,
4,

(The number of affected rows is 4)

ID name
-------------------------------
4,

(The number of affected rows is 1)

ID name
-------------------------------
3,

(The number of affected rows is 1)

ID name
-------------------------------
2

(The number of affected rows is 1)

ID name
-------------------------------
1

(The number of affected rows is 1)

-- Drop table Ta
 

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.