Case Insensitive Search Queries / Queries

Question

Does anyone know how to do case insensitive search / query using Postgres 7.4?

I was thinking about RegEx, but not sure how to do this, or maybe there is a function / flag or something that I could add to the request?

I use PHP to connect and execute queries.

So I'm looking for matching address information.

Example:

123 main street
123 Main st.
123 Main Street
123 main st
123 Main st
etc...

any thoughts?

SELECT address FROM tbl WHERE address LIKE '%123 %ain%'

Answer 1

Use ILIKEfor example:

...
WHERE 
    address ILIKE '123 main st%'

Documentation .

---

Alternatively, you can use UPPERor LOWER, for example:

...
WHERE 
    LOWER(address) LIKE '123 main st%'

Answer 2

ILIKE lower(), :

• citext: http://www.postgresql.org/docs/9.0/static/citext.html.
• - , .