Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgresql lowercase to compare data

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.

like image 289
Shadin Avatar asked Apr 29 '12 02:04

Shadin


People also ask

How do I create a lowercase in PostgreSQL?

In PostgreSQL, the LOWER function is used to convert a string, an expression, or values in a column to lower case. Let's analyze the above syntax: The LOWER function takes in value with either all uppercase or partial uppercase values or characters and convert them into lower case of the same type.

How do I make a case-insensitive in PostgreSQL?

While using regular expressions, we need to use the PostgreSQL ~* operator instead of the like operator; we can also use the ilike operator in PostgreSQL. We can also create an extension name as citext to use the case insensitive query in PostgreSQL; we need to create it first to use the extension of citext.

Is PostgreSQL data case sensitive?

PostgreSQL names are case sensitive. By default, AWS Schema Conversion Tool (AWS SCT) uses object name in lowercase for PostgreSQL. In most cases, you'll want to use AWS Database Migration Service transformations to change schema, table, and column names to lower case.

What is difference between <> and != Postgres?

<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.


1 Answers

Google is your friend:

SELECT LOWER(name) FROM user_names 
WHERE name ILIKE '%$search%' ORDER BY name ASC
like image 93
dwurf Avatar answered Oct 23 '22 03:10

dwurf