In the query statement of SQL2000 and 7.0, a larger write query method is distinguished.
--SQL2000, use the following method .-- is in the field name after adding collate chinese_prc_cs_as_ws
- Case sensitive, full half-width character
- Test Data Create Table Table (FD VARCHAR (10)) Insert INTO Table Select AA = 'Aa'Union All SELECT' AA'UNION All SELECT 'AA' - Full-angle AUNION All SELECT 'A, A' - Full Corner A , Half-angle, union all select 'a, a' - full corner A, full angle, Go
- Query - 1. Check-write letters Select * from table where fd collate Chinese_prc_cs_as_ws like '% a%' - is in the field name pl to collate chinese_prc_cs_as_ws
--2. Check the whole corner Select * from table where fd collate Chinese_prc_cs_as_ws like '% a%'
--3. Check half-angle Select * from table Where fd collate Chinese_prc_cs_as_ws like '%,%' Go
- Delete Test Data DROP TABLE Table
/*--Test Results
1. Query the result of uppercase letters FD ---------- AA
2. Query the results of all-round characters FD ---------- AAA, AA, A
3. Query the result of the half-width character FD ---------- A, A
(The number of rows affects is 1 line) - * /
============================================================================================================================================================================================================= ==============
--SQL7.0, use the following method.
- If it is all comparison - the following is the test select * from (select fd = 'a'union all select' a ') Awhere Cast (fd as varbinary (8000)) = CAST (' A 'as varbinary (8000))
/ * - Test results FD ---- a
(The number of rows affects is 1 line) - * /
- If it is partial match, use charIndex:
- Below is the test Select * from (select fd = 'a'union all select' a'union all select 'aaaa'union all select' aaaa'union all select 'ccca') Awhere Charindex (Cast ('A' as varbinary (8000)), CAST (FD AS VARBINARY (8000))> 0 / * - Test Results FD ---- AAAAACCCA
(The number of rows affects is 3 lines) - * /
