I am having a small problem with a search function in postgresql 7.1 using the LIKE '%$searchvar%' string in the select. The problem is that whats inside the %% is case sensitive to the database. If I search for matt but the database has Matt, I get no results. Currently I am using a klunky method to get around this:

$searchin[0] = explode(" ", ucwords(strtolower(addslashes($search))));
$searchin[1] = explode(" ", strtolower(addslashes($search)));
$searchin[2] = explode(" ", strtoupper(addslashes($search)));

This creates a huge variable with all the possibilities, but makes 3x the work that the database has to do when I feed it in to create the query with all the fields it looks in.

Is there a way to simply turn off the case-sensitivity?

-Matt

    Instead of using LIKE, use the pattern-matching operator ~*, i.e.

    ... where name ~ '.fred.*' ...

    which will find all rows that have the sequence 'fred' (in any combination of case) in the 'name' column.

      Thanks a ton, this really helped me out. It works great. I wish I would have gone here before pulling out all my hair trying to figure out a way to get it to work. I searched all throughout the docs can still can't find any reference to the pattern matching operator, but hey it works 🙂. thanks again, -Matt

        4 months later
        a month later

        Thanks, I have been looking for a case insensitive tag for PostgreSQL too! Works perfectly. :-)

          Write a Reply...