Case sensitvie query on case insensitive database. 
Author Message
 Case sensitvie query on case insensitive database.

I need to execute a case sensitve query against a case insensitve database.
The following example seems to give me the desired results, but I have never
seen it used before.

Ex:

Table1 contains 2 records one with 'test' the other with 'Test'.  This query
returns only the desired record!!

select * from table1 where convert(binary,col1) = convert(binary,'test')

Does anyone know of any reason why this should not be used??

TIA,

Jason



Tue, 04 Jul 2000 03:00:00 GMT
 Case sensitvie query on case insensitive database.

Jason,

There might be other reasons than this...:
When "Wrapping" a column name inside a function, SQL Server cannot make use
of an index (for that search argument).
--

MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB



Quote:
> I need to execute a case sensitve query against a case insensitve
database.
> The following example seems to give me the desired results, but I have
never
> seen it used before.

> Ex:

> Table1 contains 2 records one with 'test' the other with 'Test'.  This
query
> returns only the desired record!!

> select * from table1 where convert(binary,col1) = convert(binary,'test')

> Does anyone know of any reason why this should not be used??

> TIA,

> Jason



Fri, 07 Jul 2000 03:00:00 GMT
 Case sensitvie query on case insensitive database.

Jason,

        Tibor's warning re: performance is the only reason I know.  Your code is
_the_ way to get your results in SQL.

        Optimizations?  If the data set is big enough:
        1.      Query using a string join into a temp table.
        2.      Query the temp table with the convert(binary) join.

                                Russell Fields, MVP



Fri, 07 Jul 2000 03:00:00 GMT
 Case sensitvie query on case insensitive database.

Jason,

Quote:
>Table1 contains 2 records one with 'test' the other with 'Test'.  This query
>returns only the desired record!!

>select * from table1 where convert(binary,col1) = convert(binary,'test')

If you have an index on col1 I suggest you adjust this a bit:

select *
  from table1
 where col1 = 'test'
   and convert(binary,col1) = convert(binary,'test')

This will hopefully use the index and cut way back on the number of
rows being tested.

Roy



Sat, 08 Jul 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Case-sensitive Join on case-insensitive database

2. case insensitive searches against case sensistive fields

3. SQL Server 2000 Case Conversion to case-insensitive

4. Case sensitive on Case Insensitive DB

5. Case sensitive restored into a Case insensitive DB

6. How to use both case sensitive and case insensitive search

7. mixed case (case-insensitive) searching with FoxPro ODBC MS drivers

8. collation problem -- switch from case insensitive to case sensitive

9. v6.5 case sensitive to case insensitive ?

10. ASE case insensitive server but mixed case naming convention

11. SQL Server case sensitive or case insensitive.

12. data case-insensitive but storedprocedure identifier case-sensitive?


 
Powered by phpBB® Forum Software