pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSearching Text Fields - Case Sensitive?
5+ messages / 4 participants
[nested] [flat]
* Searching Text Fields - Case Sensitive?
@ 1999-08-12 14:59 Mike Field <mike@fieldco.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mike Field @ 1999-08-12 14:59 UTC (permalink / raw)
To: pgsql-sql
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?
Please let me know.
Thanks,
Mike
mike@fieldco.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Searching Text Fields - Case Sensitive?
@ 1999-08-12 15:24 Bart Ogryczak <bart@bart.w-wa.pl>
parent: Mike Field <mike@fieldco.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bart Ogryczak @ 1999-08-12 15:24 UTC (permalink / raw)
To: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Searching Text Fields - Case Sensitive?
@ 1999-08-13 07:48 Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
parent: Bart Ogryczak <bart@bart.w-wa.pl>
0 siblings, 2 replies; 5+ messages in thread
From: Moray McConnachie @ 1999-08-13 07:48 UTC (permalink / raw)
To: pgsql-sql
----- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Searching Text Fields - Case Sensitive?
@ 1999-08-13 09:07 Bart Ogryczak <bart@bart.w-wa.pl>
parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Bart Ogryczak @ 1999-08-13 09:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] Searching Text Fields - Case Sensitive?
@ 1999-08-13 14:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-08-13 14:27 UTC (permalink / raw)
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-08-13 14:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-12 14:59 Searching Text Fields - Case Sensitive? Mike Field <mike@fieldco.com>
1999-08-12 15:24 ` Bart Ogryczak <bart@bart.w-wa.pl>
1999-08-13 07:48 ` Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
1999-08-13 09:07 ` Bart Ogryczak <bart@bart.w-wa.pl>
1999-08-13 14:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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