Thread: Case insensitive LIKE ?
I'm writing an application that should work with PostgreSQL server on Linux and ODBC-MS Access on Windows. I'm using the TclODBC package that works just fine. Under Windows, with ODBC->MS Access , the select LIKE 'john%' works case insensitive! Under Linux with PostgreSQL 6.5.2 , the LIKE clause is case sensitive! Is there for PostgreSQL a modifier(parameter) that will make the LIKE clause case insensitive ? TIA, Constantin Teodorescu FLEX Consulting Braila, ROMANIA
Constantin Teodorescu wrote: > > I'm writing an application that should work with PostgreSQL server on > Linux and ODBC-MS Access on Windows. > > I'm using the TclODBC package that works just fine. > > Under Windows, with ODBC->MS Access , the select LIKE 'john%' works case > insensitive! > Under Linux with PostgreSQL 6.5.2 , the LIKE clause is case sensitive! > > Is there for PostgreSQL a modifier(parameter) that will make the LIKE > clause case insensitive ? Maybe select * from t where lower(name) like 'john%'; ------------ Hannu
Hannu Krosing wrote: > > Constantin Teodorescu wrote: > > > > Is there for PostgreSQL a modifier(parameter) that will make the LIKE > > clause case insensitive ? > > Maybe > > select * from t where lower(name) like 'john%'; Yes, it may work, but probably lower(name) won't work on ODBC->MS Access ... I would like to preserve also the SQL commands betwen versions and not to fill my program with IF's .. -- Constantin Teodorescu FLEX Consulting Braila, ROMANIA
Constantin Teodorescu wrote: > > Hannu Krosing wrote: > > > > Constantin Teodorescu wrote: > > > > > > Is there for PostgreSQL a modifier(parameter) that will make the LIKE > > > clause case insensitive ? > > > > Maybe > > > > select * from t where lower(name) like 'john%'; > > Yes, it may work, but probably lower(name) won't work on ODBC->MS Access > ... It is much more likely to work than ~* , the case-insensitive regex op. But to make it use indexes (for the exact case above) you should also create index on lower(name). > I would like to preserve also the SQL commands betwen versions and not > to fill my program with IF's .. I doubt that case-insensitive like is in any standard. Could someone look it up in SQL92 spec ? ------------ Hannu
Thus spake Constantin Teodorescu
> Hannu Krosing wrote:
> > select * from t where lower(name) like 'john%';
> Yes, it may work, but probably lower(name) won't work on ODBC->MS Access
Don't know about the M$ issue but if you do use the above you may want to
do the following to speed things up.
CREATE INDEX lower_name ON t (LOWER(name) text_ops);
A simple index on name won't help in the above query.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
The case insensitive equivalent in postgreSQL is: select ~* 'john' Constantin Teodorescu wrote: > > I'm writing an application that should work with PostgreSQL server on > Linux and ODBC-MS Access on Windows. > > I'm using the TclODBC package that works just fine. > > Under Windows, with ODBC->MS Access , the select LIKE 'john%' works case > insensitive! > Under Linux with PostgreSQL 6.5.2 , the LIKE clause is case sensitive! > > Is there for PostgreSQL a modifier(parameter) that will make the LIKE > clause case insensitive ? > > TIA, > Constantin Teodorescu > FLEX Consulting Braila, ROMANIA > > ************ -- Wim Ceulemans Nice bvba www.nice.be Eglegemweg 3, 2811 Hombeek Belgium Tel 0032-15-412953 Fax 0032-15-412954
> Hannu Krosing wrote: > > > > Constantin Teodorescu wrote: > > > > > > Is there for PostgreSQL a modifier(parameter) that will make the LIKE > > > clause case insensitive ? > > > > Maybe > > > > select * from t where lower(name) like 'john%'; > > Yes, it may work, but probably lower(name) won't work on ODBC->MS Access > ... > I would like to preserve also the SQL commands betwen versions and not > to fill my program with IF's .. Why not use Passthrough queries in Access/ODBC, which have pgsql do the SQL for them and therefore use pgsql's syntax? Or do you mean that the you are running Access and pgsql both as servers, rather than Access as front-end?
"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
>> I would like to preserve also the SQL commands betwen versions and not
>> to fill my program with IF's ..
> Why not use Passthrough queries in Access/ODBC, which have pgsql do the SQL
> for them and therefore use pgsql's syntax?
I think he just would rather avoid depending on unportable features,
which is a perfectly reasonable thing to want to do.
However, AFAICS there isn't any direct notion of "case insensitive LIKE"
in SQL92, so he's going to have to depend on *something* that's not in
the spec.
What the spec seems to envision is that you get this result by attaching
a case-insensitive collation spec to the column you're going to do the
LIKE on --- in other words, the meaning of "foo LIKE 'bar'" depends on
the charset and collation attributes of the foo column. If you want
something other than what the column was set up to provide, tough
cookies. Seems a tad brain-dead to me. Anyway, there's no such concept
in Postgres, and I'll bet M$ doesn't do it exactly that way either...
regards, tom lane
Wim Ceulemans wrote: > > The case insensitive equivalent in postgreSQL is: > > select ~* 'john' > I know that, I'm using on PostgreSQL the regexp search but I would like to unify also the SQL commands. -- Constantin Teodorescu FLEX Consulting Braila, ROMANIA