Case Insensitive searches/queries

Question

Does anyone know how to do a Case Insensitive Search/Query with Postgres 7.4?

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

I'm using PHP to connect and execute the queries.

So I'm looking to match 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 ILIKE, e.g.:

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

Documentation.

---

Alternatively you could use UPPER or LOWER, e.g.:

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

Answer 2

Apart from ILIKE and the lower() approach, I can see two other possibilities:

1. Use the citext data type: http://www.postgresql.org/docs/9.0/static/citext.html.
2. Use the full text search - which might actually be the most flexible and fastest solution, although a bit more complicated to get started with.