postgresql Postgresql小写来比较数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10369284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql lowercase to compare data
提问by Shadin
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.
我想从 Postgresql 数据库中的一行获取内容，并将它的小写版本与用户输入的小写版本进行比较，以检查它是否存在于数据库中。
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/#remotefor search box (for names)
我正在尝试实现一个自动完成的 Jquery UI，如下所示：http: //jqueryui.com/demos/autocomplete/#remotefor search box (for names)
using javascript and php.
使用 javascript 和 php。
php code:
php代码：
$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:
JavaScript 代码：
$(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
以上所有工作都很好..就像这里的演示一样：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.
我的问题是数据库中的名称以大写形式存储（例如 LORI），当然用户更喜欢在搜索框中插入小写字母来搜索名称（例如 lori）。但由于它以大写形式存储，我需要转换它。
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.
提前致谢。
回答by dwurf
SELECT LOWER(name) FROM user_names
WHERE name ILIKE '%$search%' ORDER BY name ASC
