Home / Questions / 205056
Accepted
fedorqui
Asked: 2020-10-17 05:36:22 +0800 CST 2020-10-17 05:36:22 +0800 CST

How can I do a query in PostgreSQL without being case sensitive?

  • 772

Turns out I want to query my PostgreSQL database for a specific name. The problem is that I don't know if that name is uppercase or lowercase, so I have to try all the combinations:

SELECT * FROM personas WHERE nombre = 'Juan'
SELECT * FROM personas WHERE nombre = 'juan'
SELECT * FROM personas WHERE nombre = 'JUAN'

As you can see, it is somewhat tedious and something like MySQL does not work for me:

SELECT * FROM personas WHERE nombre LIKE '%Juan%'

Is there a way to perform the query and get the results regardless of whether they are in uppercase or lowercase?

postgresql

1 Answers

  1. Best Answer

    Looking at PostgreSQL: How to make “case-insensitive” query I see that there are several options:

    • Using ILIKE:

      SELECT * FROM personas WHERE nombre ILIKE 'juan'

    • Using LOWER()to convert all to lowercase (optionally same with uppercase):

      SELECT * FROM personas WHERE LOWER(nombre) = 'juan'

      which is the same as

      SELECT * FROM personas WHERE LOWER(nombre) = LOWER('Juan')

    • Using POSIX regular expressions with ~*:

      SELECT * FROM personas WHERE nombre ~* 'Juan'
    • 7