I have a lot of SQL code which has been running against a case-insensitive
database fine. But, now it has to run against a case-sensitive database.

So, any ideas on how to go about making this work?

There are two ways :

1. Make everything the same case e.g. force upper case.

2. Make use of the facilities in SQL like. If you use square brackets [ ]
you can do a case insensitive search - however it is a lot slower. eg to
search for "Cat" in any case  - like '[Cc][Aa][Tt]'

--
Adroit Software Ltd
------------------------------
Remove the nospam. to mail me direct

I have built two systems, both used case-sensitive RDBMS.

In one system, we forced all char(n) fields needed to be indexed to
uppercase.  Simple but does not look as good.

In the other system, for every char(n) field to be indexed, a column is
added to store its uppercase value which is maintained by triggers.  Instead
of original field, the additional column is indexed.  The original fields is
selected, but order by the additional column.  It consumes more resources,
and requires a little more work but it looks better.

HTH,
Ping Kam

Brian,
  I think you've got Jo's meaning backwards.  Jo had a case INsensitive
database, and now uses a case sensitive one.  To handle that, I guess all
you would need to do is find anything that is already specifically written
to allow any case and remove that code or change that setting.  For example,
if there is code that compares UpperCase(field1) to UpperCase(field2), just
change it to compare field1 to field2.  And if there are any data aware
controls or tables/queries with a property set to be case insensitive, just
change them to case sensitive.
-Howard

I guess Jon should be more specific about his needs. I read it that the
queries he used to use on the case insensitive database now need to work on
one which is case sensitive. Using LIKE allows that (i.e. he can query on
the case sensitive database using non-case sensitive entries), though he
will need to update his code to use it. Informix is a case sensitive
database and this can be a pain in the neck, esp. if you are searching on
string data entered by which has not been filtered first. So I stand by my
first suggestion, though I like the idea that Ping has posted - as this
would be faster.

--
Adroit Software Ltd
------------------------------
Remove the nospam. to mail me direct

Agreed. Actually, it's a shame that some databases are case sensitive only,
IOW byte compare only.

--
Robert

Um, think again. IMO you got it backwards.
Case INsensitive means: 'A'='a'
Case sensitive means: 'A'='A'; 'A'<>'a'
So, the code that compares UpperCase(field1) to UpperCase(field2) (=case
insensitive compare) has sense with case sensitive databases, as with case
insensitive databases the database engine does this job.

--
Robert