Re: [SQL] Searching Text Fields - Case Sensitive? - Mailing list pgsql-sql

From Bart Ogryczak
Subject Re: [SQL] Searching Text Fields - Case Sensitive?
Date
Msg-id 37B3E046.278547CA@bart.w-wa.pl
Whole thread Raw
In response to Searching Text Fields - Case Sensitive?  ("Mike Field" <mike@fieldco.com>)
List 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


pgsql-sql by date:

Previous
From: "Moray McConnachie"
Date:
Subject: Re: [SQL] Searching Text Fields - Case Sensitive?
Next
From: "Hutton, Rob"
Date:
Subject: Problems with default date and time