Board index » delphi » where upper(my_field)

2008-04-22 08:48:33 PM
delphi148
Hello! Now I have installed FB1.5, but I hope this is the same with
Interbase 6
I have a table with an index on MY_CHAR_FIELD
if I do:
SELECT * FROM MY_TABLE WHERE MY_CHAR_FIELD STARTING WITH 'SOMETHING'
FB perform index reads, bu if I do
SELECT * FROM MY_TABLE WHERE Upper(MY_CHAR_FIELD) STARTING WITH 'SOMETHING'
FB don't use any index, which seems logical.
Can I create an index based on Upper(MY_CHAR_FIELD) ?
How is the correct way to do it ?
Thanks!
-sergio
 

On Tue, 22 Apr 2008 09:48:33 -0300, "Sergio Gonzalez"
<XXXX@XXXXX.COM>writes:
the release notes for syntax.
If you have any further questions you better ask them in FireBird
support list: groups.yahoo.com/group/firebird-support/
HTH
ain
 

Sergio Gonzalez writes:
dedicated to InterBase. IB and Firebird have become very different over
the years so if you want a correct answer you need to post in a forum
for the product you are using.
--
Bill Todd (TeamB)
 

"Sergio Gonzalez" <XXXX@XXXXX.COM>writes
InterBase, the only way to do this currently is to have an additional field.
This field can be populated by AfterInsert and AfterUpdate triggers that set
this additional field to uppercase automatically for you, and you can then
put an index on this field.
Then change your query to:
SELECT * FROM MY_TABLE
WHERE MY_UPPER_CHAR_FIELD STARTING WITH 'SOMETHING'
I seem to recall that there is also a collation that can be used that is
case-insensitive, but I have never personally used collations and so really
can't advise on this. Perhaps someone else can comment on that.
--
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca)