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