Tim

select * from companies order by _Name

Produces an insensitive result set even though a sensitive one was requested. There is an index called CompanyName which is _Name COLLATE ANSI_CI ASC

Why is it insensitive?

EDB 1.09b3

Roy Lambert

Roy,

<< Why is it insensitive? >>

Because the implicit collation of the sort specification doesn't match the
collation of the available index.

The default collation in EDB is ANSI (case sensitive). To get a sensitive
result set, you must explicitly specify the matching collation:

select * from companies order by _Name collate ANSI_CI

Ole Willy Tuv

Roy

I have realized that it is interesting to define COLLATE at field level and
you can use the SQL statement without any COLLATE clause.

Eduardo

Ole


So simple when you know

Roy Lambert

Eduardo,

<< I have realized that it is interesting to define COLLATE at field level
and you can use the SQL statement without any COLLATE clause. >>

Yes, and you're also guaranteed that both indexed and non-indexed searches
will return the same data.

--
Tim Young
Elevate Software
www.elevatesoft.com

Jose


Yeah, I've decided all of my VARCHAR's are becoming ANSI_CI - should make life a lot easier, at least for SQL for Delphi I'll just need to LowerCase both sides of comparisons.

Roy Lambert