Query Case Sensitive

Case


in SQL2000 and 7.0 query statements, distinguish uppercase query methods





--sql2000, use the following method.


--that is, add collate chinese_prc_cs_as_ws after the field name








--a method for distinguishing case and full half-width characters





--Test data


CREATE TABLE (FD varchar (10))


INSERT INTO Table


select aa= ' AA '


UNION ALL SELECT ' Aa '


UNION ALL SELECT ' AA '--Full angle a


UNION ALL SELECT ' A,a '-full angle A, half-width,


UNION ALL SELECT ' A,a '-full angle A, full-width,


Go





--Query


--1. Check Capital Letter


select * FROM table


where FD collate chinese_prc_cs_as_ws like '%a% '


--that is, add collate chinese_prc_cs_as_ws after the field name





--2, full angle


select * FROM table


where FD collate chinese_prc_cs_as_ws like '%a% '





--3, Half corner


select * FROM table


where FD collate chinese_prc_cs_as_ws like '%,% '


Go





--Delete test data


drop table





/*--Test Results





1. Query the results of uppercase letters


FD


----------


Aa








2. Query the results of Full-width characters


FD


----------


AA


A,a


A,a








3. Query the results of Half-width characters


FD


----------


A,a





(the number of rows affected is 1 rows)


--*/








================================================================





--sql7.0, use the following method.





--If it's all comparative


--Here is the test


select * FROM (


Select Fd= ' A '


UNION ALL SELECT ' A '


) A


where CAST (FD as varbinary (8000)) =cast (' A ' as varbinary (8000))





/*--Test Results


FD


----


A





(the number of rows affected is 1 rows)


--*/





--if it's a partial match, use CHARINDEX:





--Here is the test


select * FROM (


Select Fd= ' A '


UNION ALL SELECT ' A '


UNION ALL SELECT ' AAaa '


UNION ALL select ' AAAA '


UNION ALL SELECT ' Ccca '


) A


where CHARINDEX (cast (' A ' as varbinary (8000)), CAST (FD as varbinary (8000)) >0





/*--Test Results


FD


----


A


AAaa


Ccca





(the number of rows affected is 3 rows)


--*/