How to make the query case sensitive in MS SQL2000

Refer to sorting rules:

198 chinese_prc_bin

199 chinese_prc_ci_as

Select * From sysobjects where name collate chinese_prc_bin = n 'sysobjects'

Select * From sysobjects where name collate chinese_prc_ci_as = n 'sysobjects'

Alter database collate chinese_prc_cs_as

Modify the sorting rule to a case-sensitive sorting rule.

If only one table is modified, use the alter table statement.

If you modify the default sorting rules of a database, use the alter datebase statement.

If you modify the sequence rules of the entire server, use rebuildm.exe to recreate the master database.

-- Specify the sorting rule.

-- Example

Select Replace ('abac' collate chinese_prc_cs_as_ws, 'A', '')

-- If you require table support, you can specify the sorting rules when creating the table so that replace does not need to write the sorting rules.

-- Example

Create Table Tb (A varchar (20) Collate chinese_prc_cs_as_ws)

Insert TB values ('abac ')

Select Replace (A, 'A', 'test') from TB

Drop table TB

Specify the sorting rule.

Windows sorting rule name

Specify the Windows sorting rule name in the Collate clause. The Windows sorting rule name consists of the sorting rule indicator and comparison style.

Syntax

<Windows_collation_name >::=

Collationdesignator _ <comparisonstyle>

<Comparisonstyle >::=

Casesensitivity_accentsensiti.pdf

[_ Kanatypesensitive [_ widthsensitive]

| _ Bin

Parameters

Collationdesignator

Specifies the basic sorting rules used by Windows sorting rules. Basic sorting rules include:

Specifies the alphabet or language in which the sorting rules are applied when sorting by dictionary is specified.

Used to store non-UNICODE character dataCodePage.

For example, latin1_general or French, both use the code page 1252 or Turkish, and use the code page 1254.

Casesensiti.pdf

The CI parameter is case insensitive and the CS parameter is case sensitive.

Accentsensiti.pdf

AI specifies no accent, and as specifies the accent.

Kanatypesensitive

Omitted specifies case-insensitive, and Ks specifies a Kana type.

Widthsensiti.pdf

Omitted specifies case-insensitive, and WS specifies case-sensitive.

Bin

Specify the binary sorting order.