As a side note, if you want to add case insensitive searching, you might want to think about doing something like the following (Using the cities example)

create table cities(
city varchar(30),
city_upper varchar(30),
blah blah blah);

Then create triggers to manage the city_upper field for inserts and updates (So you don't have to worry about it). All the triggers would have to do is set city_upper = upper(city). You can even go so far on the update triggers to see if old.city is different from new.city, and only update if they are different, though I'm not sure if the cost of doing that is greater than just setting it. You can then create an index on the city_upper field.
Everyone will tell you that indexed searching is better than non-indexed filtering, especially if the size of your dataset is growing. If you do something like "select city from cities where upper(city) = 'NEW YORK'", you don't use any indexes that you might have for the city field. Hence the end-run with the uppercase field and the index on it.
A select statement might look like:

select city,city_upper from cities where city_upper = 'NEW YORK' order by city_upper;

You might even be able to do:

select city from cities where city_upper = 'NEW YORK' order by city_upper;

(I'm not sure about the second select. I'm not near a computer where I can test it out. I know the statement has issues with MS SQL Server, because SQL Server needs all fields you order by to be in your select statement.. Since we support both IB/FB and MSSQL, we always do the first way).
We have this scheme set up for case insensitive searching and it works quite well for us.

- Jason

----- Original Message -----
From: Joe Martinez
To: ib-support@yahoogroups.com
Sent: Sunday, June 02, 2002 1:06 PM
Subject: Re: [ib-support] ignore case order by


I don't believe there's a particular way to tell FB to do a case insensive
order by, but here's what I do:

Let's say I want to bring up a list of cities from my cities table. I'll do
something like:

select upper(city) from cities
order by 1

If you want to preserve the original capitalization in your results, you can
select the non-upper'ed version as well like:

select upper(city),city from cities
order by 1

And then just use the second column for your purposes.

-Joe

Massimo Ferrari wrote:

> Hello,
> how can I tell Firebird via SQL to perform an ignore case order by?
> thank you
> Massimo


Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]