Case sensitive!
lazarus / 16-Databases

----------------------------------------------------------------------
okhsar · 2012-01-29 04:01

Hello All,

I am using following code to read a value form an edit box and use it
to select from table all matching records:

procedure TForm1.Edit5EditingDone(Sender: TObject);
begin
  Q1.active:= false;
  Q1.sql.clear;
  Q1.SQL.Text:= 'select * from ticketin where lname = :lname';
  Q1.Params.ParamByName('lname').AsString := Edit5.text;
  Q1.active:= true;
end; 

Lets say I enter in edit box "Smith".  If the table contains "SMITH",
then it will not be selected because the case does not match.  How can
I make the selection case insensitive?  I presume the commands like
and upper would somehow work, but I don't know the syntax when using
ParamByName.  Appreciate any help.

Thanks,
Okhsar

----------------------------------------------------------------------
ludob · 2012-01-29 06:32

QuoteLets say I enter in edit box "Smith".  If the table contains
"SMITH", then it will not be selected because the case does not
match.That depends on the database engine and the collation used.
MySQL fe. uses a case insensitive collation (latin1_swedish_ci) by
default and comparisons are case insensitive (for the character set
supported by the collation).

The only reliable solution is to use ucase (or upper depending on the
db server) for both sides of the equation:
Code: [Select]'select * from ticketin where ucase(lname) =
ucase(:lname)
This way you respect the collation selected in the database.

EDIT: Note that above is the direct answer to your question but
probably not the best solution for your problem. Doing a where
'ucase(lname) = ucase(:lname)' is doing a full table scan and,
supposing lname is used in an index, is going to be much slower than a
'lname = :lname'. Unless you need the case sensitive sort order, the
better solution is definitely to use a case insensitive collation for
the database and/or table and/or field (most databases allow for a
very finely grained definition of collations). Knowing what database
you are using would help

----------------------------------------------------------------------
okhsar · 2012-01-29 07:24

ludob,

I am using a firebird database.  And you are correct, I really want to
avoid full table scan.

Thanks,

----------------------------------------------------------------------
ludob · 2012-01-29 08:08

Depending on the firebird version you have different solutions. Here
is a article:
http://www.destructor.de/firebird/caseinsensitivesearch.htm.
describing the different possibilities. Note that the "Expression
Index" available from 2.0 is an elegant solution for an existing
database. No need to re-create or alter the table and you have both
case sensitive and case insensitive sorting. Pay attention to the
"exactly the same expression" warning. Oracle calls this "function
based indexes" and here
http://www.akadia.com/services/ora_function_based_index_2.html you
find a nice description of the impact on execution plans and the pros
and cons of these indexes.

----------------------------------------------------------------------
okhsar · 2012-01-29 09:38

Thanks very much.  problem solved.

Okhsar