If I remember correctly, when MS SQL Server is installed one determines
whether the DBs will be case sensitive or case insentitive. If the former
is selected, you have to cast or convert everything to upper or lower case
when doing compares as 'a' != 'A'. This of course forces table scans as
placing any sort of evaluation on the left side of the = prevent the use of
an index and you cannot force an index to be case insensitive (I think you
can do this in Oracle). I suspect that the reverse is true in the below
example. If you are casting your varchar as case sensitive, it may not be
able to use the indexes properly. I am not positive, but it is worth
looking into if the tables are large as it will cause a performance hit.
Justin
-----Original Message-----
From: Alex Santantonio [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 31, 2001 9:59 AM
To: CF-Talk
Subject: RE: case sensitive sql query
SQL Queries ARE NOT case sensitive when doing a WHERE that involves a
varchar. The way to do a Case sensitive WHERE clause depends largely on the
database that you are using. If you are using SQL Server 7/2000 then it is
quite simple. It is even more simple if you are using Stored Procedures to
perform this task. Just use CAST to force your value to binary data and
then compare based on the returned binary data. CAST(ColumnName AS
VARBINARY(ColumnLength)) = CAST('Test Query' AS VARBINARY(ColumnLength)) in
your WHERE within your Stored Procedure. Once you have that set, just make
the 'Test Query' string in your Stored Procedure into a @sp_variable and
pass in the value you are searching on. CAST is preferred over CONVERT.
Alex Santantonio,
Lead Developer
Macromedia Coldfusion 5 Certified Professional
Macromedia Certified Web Site Developer
[EMAIL PROTECTED]
www.doceus.com
-----Original Message-----
From: cf refactoring [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 31, 2001 9:11 AM
To: CF-Talk
Subject: Re: case sensitive sql query
SQL SELECT queries ARE case sensitive.
The problem is in your data -- you're using columnames
from your tables as data. Depending on the database,
this information is stored in auxiliary tables (in
Oracle, it would be in a table like
"all_tab_columns"), and may be in a case-insensitive
form. For example, in Oracle, if I run
select column_name, table_name from all_tab_columns
then I see that all the column_names and table_names
are all uppercase even though when I created the
tables, I had mixed case for the table and column
names
--- Bernd VanSkiver <[EMAIL PROTECTED]> wrote:
> Is there a way to do a case sensitive SELECT query?
> Don't want to make a
> server wide change, just want to specify a single
> query to do a case
> sensitive search.
>
> SELECT ColumnName
> FROM tableName
> WHERE ColumnName = 'Test Query'
>
> Want this to do a case sensitive search on the WHERE
> clause.
>
> Bernd VanSkiver
> [EMAIL PROTECTED]
> ColdFusion Developer
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
