pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedCase insensitive LIKE ?
11+ messages / 8 participants
[nested] [flat]
* Case insensitive LIKE ?
@ 1999-10-31 12:10 Constantin Teodorescu <teo@flex.ro>
0 siblings, 2 replies; 11+ messages in thread
From: Constantin Teodorescu @ 1999-10-31 12:10 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 12:30 Constantin Teodorescu <teo@flex.ro>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 3 replies; 11+ messages in thread
From: Constantin Teodorescu @ 1999-10-31 12:30 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 12:35 Hannu Krosing <hannu@tm.ee>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 1 reply; 11+ messages in thread
From: Hannu Krosing @ 1999-10-31 12:35 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 12:54 Hannu Krosing <hannu@tm.ee>
parent: Constantin Teodorescu <teo@flex.ro>
2 siblings, 0 replies; 11+ messages in thread
From: Hannu Krosing @ 1999-10-31 12:54 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 13:29 D'Arcy J.M. Cain <darcy@druid.net>
parent: Constantin Teodorescu <teo@flex.ro>
2 siblings, 0 replies; 11+ messages in thread
From: "D'Arcy" "J.M." Cain @ 1999-10-31 13:29 UTC (permalink / raw)
To: teo@flex.ro; +Cc: hannu@tm.ee; pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 16:31 Wim Ceulemans <wim.ceulemans@nice.be>
parent: Constantin Teodorescu <teo@flex.ro>
1 sibling, 1 reply; 11+ messages in thread
From: Wim Ceulemans @ 1999-10-31 16:31 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 17:01 Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
parent: Constantin Teodorescu <teo@flex.ro>
2 siblings, 1 reply; 11+ messages in thread
From: Moray McConnachie @ 1999-10-31 17:01 UTC (permalink / raw)
To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces
> 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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 1999-10-31 17:29 UTC (permalink / raw)
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: Constantin Teodorescu <teo@flex.ro>; pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 19:31 Ansley, Michael <Michael.Ansley@intec.co.za>
0 siblings, 0 replies; 11+ messages in thread
From: Ansley, Michael @ 1999-10-31 19:31 UTC (permalink / raw)
To: 'Constantin Teodorescu ' <teo@flex.ro>; 'Hannu Krosing ' <hannu@tm.ee>; +Cc: pgsql-interfaces
Hi, Constantin,
I'll be very surprised if you can get the whole lot to work without using
IFs. Access is markedly non-SQL compliant. It pushes the term 'extended
sub-set' to the limit, both the 'extended', and the 'sub' bits. I work with
Access and Oracle at work, and everything has to either be done for Access,
or Oracle. Not to appear negative or anything...
I have just tried Access now, and it doesn even know what % is. It seems to
hand the like clause to the underlying engine for evaluation, when I tried
it with an Oracle linked table, it was quite happy, case-sensitive and all.
With a native Access table, it wasn't interested in the slightest.
So, that might point to a difference in the ODBC drivers' implementation and
handling of regexes.
MikeA
-----Original Message-----
From: Constantin Teodorescu
To: Hannu Krosing
Cc: PostgreSQL Interfaces
Sent: 99/10/31 02:30
Subject: Re: [INTERFACES] Case insensitive LIKE ?
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
************
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: [INTERFACES] Case insensitive LIKE ?
@ 1999-10-31 20:31 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 11+ messages in thread
From: Dave Page @ 1999-10-31 20:31 UTC (permalink / raw)
To: pgsql-interfaces
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 31 October 1999 17:30
> To: Moray McConnachie
> Cc: Constantin Teodorescu; PostgreSQL Interfaces
> Subject: Re: [INTERFACES] Case insensitive LIKE ?
<Snip>
> 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.
I've 'caught' MS DAO retrieving entire tables and filtering the recordset
locally to satisfy a LIKE clause before now (it may have been a MATCHES).
This does give the advantage of working with any backend db of course, but
may (has) cause(d) problems with other frontends.
I don't know much about Access, but up to date versions of ADO (which I
assume at least Access 2000 uses) don't seem to give 2 hoots about query
syntax - they will send it to the backend and just accept a recordset, error
or nothing in return.
Regards,
Dave.
--
Network & Systems Manager, The Vale Housing Association.
http://www.vale-housing.co.uk (Work)
http://www.pgadmin.freeserve.co.uk (Home of pgAdmin)
Beer can be a permanent solution, but only if you have enough of it!
>
>
> "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.
>
> regards, tom lane
>
> ************
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [INTERFACES] Case insensitive LIKE ?
@ 1999-11-01 06:45 Constantin Teodorescu <teo@flex.ro>
parent: Wim Ceulemans <wim.ceulemans@nice.be>
0 siblings, 0 replies; 11+ messages in thread
From: Constantin Teodorescu @ 1999-11-01 06:45 UTC (permalink / raw)
To: Wim Ceulemans <wim.ceulemans@nice.be>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~1999-11-01 06:45 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-31 12:10 Case insensitive LIKE ? Constantin Teodorescu <teo@flex.ro>
1999-10-31 12:35 ` Hannu Krosing <hannu@tm.ee>
1999-10-31 12:30 ` Constantin Teodorescu <teo@flex.ro>
1999-10-31 12:54 ` Hannu Krosing <hannu@tm.ee>
1999-10-31 13:29 ` D'Arcy J.M. Cain <darcy@druid.net>
1999-10-31 17:01 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1999-10-31 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-31 16:31 ` Wim Ceulemans <wim.ceulemans@nice.be>
1999-11-01 06:45 ` Constantin Teodorescu <teo@flex.ro>
1999-10-31 19:31 RE: [INTERFACES] Case insensitive LIKE ? Ansley, Michael <Michael.Ansley@intec.co.za>
1999-10-31 20:31 RE: [INTERFACES] Case insensitive LIKE ? Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox