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

score 17 · Accepted Answer · answered Jan 20 '11 at 21:28

17

Use ILIKE, e.g.:

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

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

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

answered Jan 20 '11 at 21:28

mechanical_meat

1

Don't forget to add an index on lower(address) to speed up the search (although an index won't help for `%foo%`, only for `foo%`) – Jan 20 '11 at 22:31

score 2 · Answer 2 · answered Jan 20 '11 at 22:34

2

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

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

answered Jan 20 '11 at 22:34

I'm not sure about Citext being compatible with Postgres 7.4 and FTS does look like it would be a great solution, the efforts to get this running correctly might exceed the need. I've never really used FTS before, did you recommend and articles on the subject that might be geared towards street address? – Phill Pafford Jan 21 '11 at 15:13
Sorry I didn't see that you are using such an outdated version. I'd say you should upgrade first, and then proceed. FTS is definitely not an option for 7.x. You should really, really update to at least 8.4 – Jan 21 '11 at 15:39
would if I could but not really my choice :( – Phill Pafford Jan 21 '11 at 16:16

2 Answers2