SELECT question

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!

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.

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%';

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!

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

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: