Обсуждение: 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