CODEWITHSUNDEEP
sqlregexpostgresqlcase-insensitive
Phill Pafford
Phill Pafford

Reputation: 85318

Case Insensitive searches/queries

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

Upvotes: 5

Views: 9094

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 2

mechanical_meat
mechanical_meat

Reputation: 169314

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

Upvotes: 17

Related Questions