Board index » delphi » Is SQL non case sensitive possible?

Is SQL non case sensitive possible?

All  32 bit SQL searches appear to be case sensitive.
Is it possible to do a 32 bit SQL non case sensitive search?

The closest I can come is:

Query1.SQL.ADD(Format('Select * FROM KEEPDELP WHERE %s LIKE "%%%s%%"',
[UpperCase(SrchFld.Text), UpperCase(SrchStr.Text)]));

SrchFld is the name of a Database field in a TComboBox. SrchStr.Text
is the search string in a TEdit box.

(The query works fine as a wild card case sensitive if the
"UpperCase"'s are removed.)

The problem is the "UpperCase(SrchFld.Text)" which does not appear to
turn the contents of the Field to uppercase, and I cannot see how to do
that, assuming of course that is possible.

I would appreciate comments or help with any other methods to do an
SQL non case sensitive search.

Thanks-
Ted--

 

Re:Is SQL non case sensitive possible?


Quote
Hugh Birckhead <bi...@worldnet.att.net> wrote:
>All  32 bit SQL searches appear to be case sensitive.
>Is it possible to do a 32 bit SQL non case sensitive search?

Well, the mainframe (IBM) SQL-DS I use at work distinguishes case sensitivety by
enclosing the search argument in either single quotes (') - use case sensitive
search; or double quotes (") - use case insensitive search.  Tried this with
both 16 and 32 bit BDEs, no soap.  Guess you are sticu with either
1 Storing all entries in upper case and forcing upper case on all arguments, or
2. Forcing upper case on BOTH user entry and datbase contents before comparing.

Bummer.

Quote

>The closest I can come is:

>Query1.SQL.ADD(Format('Select * FROM KEEPDELP WHERE %s LIKE "%%%s%%"',
>[UpperCase(SrchFld.Text), UpperCase(SrchStr.Text)]));

>SrchFld is the name of a Database field in a TComboBox. SrchStr.Text
>is the search string in a TEdit box.

>(The query works fine as a wild card case sensitive if the
>"UpperCase"'s are removed.)

>The problem is the "UpperCase(SrchFld.Text)" which does not appear to
>turn the contents of the Field to uppercase, and I cannot see how to do
>that, assuming of course that is possible.

>I would appreciate comments or help with any other methods to do an
>SQL non case sensitive search.

>Thanks-
>Ted--

Re:Is SQL non case sensitive possible?


Quote
> Well, the mainframe (IBM) SQL-DS I use at work distinguishes case sensitivety by
> enclosing the search argument in either single quotes (') - use case sensitive
> search; or double quotes (") - use case insensitive search.  Tried this with
> both 16 and 32 bit BDEs, no soap.  Guess you are sticu with either
> 1 Storing all entries in upper case and forcing upper case on all arguments, or
> 2. Forcing upper case on BOTH user entry and datbase contents before comparing.
> Thanks for the reply-

The best solution appears to be using the SQL "UPPER" method-

Ted--

Other Threads