RE: [INTERFACES] Case insensitive LIKE ?

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)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
>
> ************
>