Thread: Searching Text Fields - Case Sensitive?

> Mike Field wrote:
> Hi-
> If I want to search a text field for a certain word, say 'foobar',
> using:
> SELECT * FROM mydb WHERE description LIKE "%foobar%";
> it seems to be case sensitive.  Is there a way to do it so it is NOT
> case-sensitive?
SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%";

bart
-- 
bart@bart.w-wa.pl || http://www.bart.w-wa.pl

I am a SQL newbie, so hang in there with me.

I have two fields in a table, "filename" and "revision".
The table contains 3 rows where the filename is the same and
revision contains "1" "2" and "3" in each respective row.
It also contains another row for a different filename with
revision set to "1".

I want to do a select which only returns the highest revision
level for each filename, so the select will return two rows,
the first filename with revision = "3" and the second filename
with revision set to "1".

How do I do this? I imagine this is simple, but it is new to me.

Thanks.

"Frank Morton" <fmorton@base2inc.com> writes:
> I want to do a select which only returns the highest revision
> level for each filename,

Try something like
SELECT filename, max(revision) FROM table GROUP BY filename;

When you use GROUP BY, aggregate functions like max() are applied
separately over each group --- which is just what you want for this
problem.
        regards, tom lane

----- Original Message -----
From: Bart Ogryczak <bart@bart.w-wa.pl>
To: <pgsql-sql@postgreSQL.org>
Sent: Thursday, August 12, 1999 4:24 PM
Subject: Re: [SQL] Searching Text Fields - Case Sensitive?


> > SELECT * FROM mydb WHERE description LIKE "%foobar%";
> > it seems to be case sensitive.  Is there a way to do it so it is NOT
> > case-sensitive?
> SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%";

Brings up an interesting question. Which is faster, to use the lower/upper
functions as above (and of course
if foobar is a variable, it would also have to be made lower case by the
program calling pgsql), or to use the pattern matching case-insensitive
operator?

Yours,
Moray

Moray McConnachie wrote:

> > > SELECT * FROM mydb WHERE description LIKE "%foobar%";
> > > it seems to be case sensitive.  Is there a way to do it so it is NOT
> > > case-sensitive?
> > SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%";
> 
> Brings up an interesting question. Which is faster, to use the lower/upper
> functions as above 
[...]
> or to use the pattern matching case-insensitive operator?LIKE seems to be much simpler then the regex pattern
matching,
so IMHO, the lower/upper & LIKE combination may be faster for short
fields,
but probably might be slower on very long text fields (when it has to
convert
whole thing to lower/upper case, before doing LIKE). Anyway, the differences are probably almost none either way.

bart
-- 
bart@bart.w-wa.pl || http://www.bart.w-wa.pl

"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
>> SELECT * FROM mydb WHERE lower(description) LIKE "%foobar%";

> Brings up an interesting question. Which is faster, to use the
> lower/upper functions as above (and of course if foobar is a variable,
> it would also have to be made lower case by the program calling
> pgsql), or to use the pattern matching case-insensitive operator?

Offhand I'd guess that the case-insensitive regex would be faster than
the above, because it avoids the overhead of making an extra function
call.  (Functions that return text, or any other variable-length data
type, require a memory allocation step --- for typical string lengths
I'd imagine the malloc costs more than the actual character-slinging...)

*However*, the situation changes considerably if you have an index
on lower(description) and a pattern that allows the index to be used.
The above pattern does not, but any left-anchored pattern, say
'foobar%', would allow index restriction clauses to be generated and
used.  The effective query with a left-anchored pattern is like this:
... WHERE lower(description) LIKE "foobar%" AND      lower(description) >= "foobar" AND      lower(description) <
"foobas";

(actually there are some character-set issues that complicate matters,
but that's the basic idea).  When you have an index on
lower(description), the system can and will use the last two clauses
to restrict an indexscan so that tuples outside the range foobar to
foobas are never even fetched.  Obviously, this can make for a speedup
of orders of magnitude, swamping any nitpicky little questions of how
fast a particular expression can be evaluated.

If you use a case-insensitive regex then this doesn't work, even for a
left-anchored pattern ('^foobar'), because the system knows that 'f' can
match either 'f' or 'F' so it can't generate the index restriction
clause...
        regards, tom lane