Postgresql lowercase to compare data

Question

I want to get the contents from a row in the Postgresql database and compare the lowercase version of it to a lowercase version of a user input to check if it exists in the database.

i tried:

"SELECT LOWER(name) FROM user_names WHERE name LIKE '%$search%' ORDER BY name ASC"

but that make query not working at all.

EDIT

I am trying to implement an autocomplete Jquery UI like here: http://jqueryui.com/demos/autocomplete/#remote for search box (for names)

using javascript and php.

php code:

$search = ($_GET['term']);
       if (!$con)
        { die('Could not connect: ' . pg_last_error ());}

       else
        {

        $sql = "SELECT name FROM users_table WHERE name LIKE '%$search%' ORDER BY name ASC";
        $result = pg_query($sql);
        $json = '[';
        $first = true;
        while ($row = pg_fetch_array($result))
        {

        if (!$first) { $json .=  ','; } else { $first = false; }
        $json .= '{"value":"'.$row['name'].'"}';
    }
    $json .= ']';
    echo $json;

    exit();

    }

JavaScript code:

   $(document).ready(function()
    {
        $('#auto').autocomplete(
        {
            source: "./file.php",
            minLength: 3

        })

})

all above work great.. exactly like in Demo here: http://jqueryui.com/demos/autocomplete/#remote

my problem is that the names in database stored in Uppercase (e.g. LORI) and of course the user prefers to insert a lowercase in search box to search for name (e.g. lori). but since it stored in uppercase, i need to convert it.

i tried as your suggestion :

$sql = "SELECT LOWER(name) FROM users_table WHERE name ILIKE '%$search%' ORDER BY name ASC";

then i got an empty drop down list! pretty weird!

thanks in advance.

You need to set name to lower in the where statement not the select `LOWER(name) ` — Daniel L. VanDenBosch, Nov 16 '18 at 19:24

score 20 · Accepted Answer · answered Apr 29 '12 at 02:48

20

Google is your friend:

SELECT LOWER(name) FROM user_names 
WHERE name ILIKE '%$search%' ORDER BY name ASC

answered Apr 29 '12 at 02:48

dwurf

12,393
6
30
42

thanks but still i got an empty array from JSON if i use LOWER – Shadin Apr 29 '12 at 05:17
@Shadin Sorry, I wasn't very clear in my post. The suggestion is to change `LIKE` to `ILIKE` – dwurf Apr 29 '12 at 05:21
yes i noticed that and tried it. i do not undersatnd why i got an empty array – Shadin Apr 29 '12 at 10:13
2

It works here: http://sqlfiddle.com/#!1/f6f97/3 can you post more details about your schema? The language you're using? The problem you're trying to solve? – dwurf Apr 29 '12 at 10:36
I'm sorry @Shadin, I just can't see anything wrong here. I've created my own page using the code you posted and it's working fine. Here's the code I used, maybe this can help you https://gist.github.com/2554894 – dwurf Apr 30 '12 at 02:05
1

THANK YOU SO MUCH dwurf !! it works when i removed 'lower' and just use ILIKE !! i really appreciate your help\1 – Shadin Apr 30 '12 at 05:22

Postgresql lowercase to compare data

1 Answers1