Subject Re: Case Insensitivity
Author Helen Borrie
At 03:42 PM 4/04/2003 -0500, Jack Mason wrote in private email:
>Thanks. I greatly appreciate your help. It appears a lot more
>complicated than what we want to do. The only character in the field that
>can be upper or lower case is an 'x', so we currently set two strings, and
>if one ends in 'x', we set the other to end in 'X' and our search is then:
>'(ISBN=''' + ISBN_Text1 + ''' OR ISBN=''' + ISBN_Text2 + ''')'

Is ISBN in the statement above a Delphi variable? Can't tell what you are
doing here. If it is a WHERE predicate, then you can't use '+' to
concatenate in SQL. The concatenator is the double-pipe, '||'. But it
doesn't make any sense to concatenate quotes to a search string, anyway.

An OR predicate is fine if you would only have two possible values and you
can predict what they would be. It will use the index. Your predicate
would be:

WHERE ISBN = :param1 or ISBN = :param2

You would pass values to the predicate as follows:

MyQuery.ParamByName('param1') AsString := ISBN_VAR;
MyQuery.ParamByName('param1') AsString := Upper(ISBN_VAR);

where ISBN_VAR is the input you captured from the user.

If you want to find the ISBN numeral string, regardless of the character on
the end, you could just use STARTING WITH, which will use the index, e.g.

select <fieldlist>, ISBN from aTable

You would need a function to strip off the alpha character at the end.

I'm quite curious as to why you have ISBN codes with characters at the end,

Please keep your threads in the list unless invited to email privately. I
charge $$$$ for private consultancy! :-))