Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT question

SELECT question

From
Brian Avis
Date:
I want to run a SELECT on a text field and match any of the following.

brian
Brian
BRIAN

Or any other combination.   How do I tell SQL to ignore case when doing
a SELECT?


--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
Have a nice diurnal anomaly!

Re: SELECT question

From
Brian Avis
Date:
Assuming the data in the text field will resemble any of the following.

Brian K. Avis
Brian Avis
Brian

Or whatever combo.


Brian Avis wrote:

> I want to run a SELECT on a text field and match any of the following.
>
> brian
> Brian
> BRIAN
>
> Or any other combination.   How do I tell SQL to ignore case when
> doing a SELECT?
>
>

--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
Have a nice diurnal anomaly!

Re: SELECT question

From
Devrim GUNDUZ
Date:
Hi,

On Fri, 2003-01-31 at 01:11, Brian Avis wrote:
> I want to run a SELECT on a text field and match any of the following.
>
> brian
> Brian
> BRIAN
>
> Or any other combination.   How do I tell SQL to ignore case when doing
> a SELECT?

SELECT * FROM table WHERE column ILIKE 'brian%';

should work.

Best regards,
--
Devrim GUNDUZ
www.gunduz.org

Re: SELECT question

From
Brian Avis
Date:
That did it.  Thanks.


Devrim GUNDUZ wrote:
Hi,

On Fri, 2003-01-31 at 01:11, Brian Avis wrote: 
I want to run a SELECT on a text field and match any of the following.

brian
Brian
BRIAN

Or any other combination.   How do I tell SQL to ignore case when doing 
a SELECT?   
SELECT * FROM table WHERE column ILIKE 'brian%';

should work.

Best regards,
--
Devrim GUNDUZ
www.gunduz.org



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
 

-- 
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
Have a nice diurnal anomaly!

Re: SELECT question

From
"scott.marlowe"
Date:
On Thu, 30 Jan 2003, Brian Avis wrote:

> Assuming the data in the text field will resemble any of the following.
>
> Brian K. Avis
> Brian Avis
> Brian
>
> Or whatever combo.
>
>
> Brian Avis wrote:
>
> > I want to run a SELECT on a text field and match any of the following.
> >
> > brian
> > Brian
> > BRIAN
> >
> > Or any other combination.   How do I tell SQL to ignore case when
> > doing a SELECT?

IF you're running 7.3.x, then you might need to use a tardis to go back in
time and make sure you used the C locale when you initdb'd your database.
If you didn't do that, then you'll need to backup your database and
reinitdb it with the C locale, then reload your data to make this work.
If you're on 7.2 and before, you won't have to reload your data to make
this next bit work...

Use a select with ilike:

select * from table where field ilike '%brian%';

should work.

Re: SELECT question

From
"scott.marlowe"
Date:
On Thu, 30 Jan 2003, Brian Avis wrote:

> Assuming the data in the text field will resemble any of the following.
>
> Brian K. Avis
> Brian Avis
> Brian
>
> Or whatever combo.
>
>
> Brian Avis wrote:
>
> > I want to run a SELECT on a text field and match any of the following.
> >
> > brian
> > Brian
> > BRIAN
> >
> > Or any other combination.   How do I tell SQL to ignore case when
> > doing a SELECT?

the other method, which I forgot to add to my previous post is to user
lower()

select * from table where lower(name) like '%bruce%';

Re: SELECT question

From
Medi Montaseri
Date:
You can also use regular expression (which I am very happy to see PG has
adopted).

You can say

select whatever from whereever where something ~* 'brian';

The style is very similar to Perl's adoption of Regular Expression, its
called
Learn-Once-Use-Many.....

See page 77 of "PostgreSQL Developer's Handbook, SAMS" or your nearest
PG web page.

scott.marlowe wrote:

>On Thu, 30 Jan 2003, Brian Avis wrote:
>
>
>
>>Assuming the data in the text field will resemble any of the following.
>>
>>Brian K. Avis
>>Brian Avis
>>Brian
>>
>>Or whatever combo.
>>
>>
>>Brian Avis wrote:
>>
>>
>>
>>>I want to run a SELECT on a text field and match any of the following.
>>>
>>>brian
>>>Brian
>>>BRIAN
>>>
>>>Or any other combination.   How do I tell SQL to ignore case when
>>>doing a SELECT?
>>>
>>>
>
>the other method, which I forgot to add to my previous post is to user
>lower()
>
>select * from table where lower(name) like '%bruce%';
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>