RE: [INTERFACES] Case insensitive LIKE ?

> -----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
> 
> ************
>