如何在查询中使用LIKE和_？

Question

i've a sql database with thousands of URLs. And i constantly update it without any modification in the Url using php5.

now, i'd like the add only the url which are not present in the database, i do this by check the database using this sql query

$q = $urlToCheck;
SELECT * FROM pligg WHERE rawUrl LIKE '$q%'

Here is the problem, some of the Urls which are already in the database are like

http://example.com/a_b_c.html
http://example.com/a1b1c.html

if i run the query

$q = 'http://example.com/a_b';
SELECT * FROM pligg WHERE rawUrl LIKE '$q%'

then it will return both the Urls. I want to return only the exact match Url. So, i want to get only this url

http://example.com/a_b_c.html

any help?

Answer 1

For MySQL you need to escape the _; it should be \_. For MSSQL it should be [_]

EDIT
Here's the manual page on _ and escaping wildcards as % and _ for MySQL. You can actually specify the escape character if you like.

$q = 'http://example.com/a_b';
$qescaped = str_replace('_', '\_', $q);

$result = my_execute_query_function($qescaped);

Here's the manual page for MSSQL.

$qescaped = str_replace('_', '[_]', $q);

(Also: MSSQL also allows you to specify the escape character (which I didn't know)).

Other RDBMS'es like PostgreSQL, Oracle etc. might have their own escape characters and/or ways of specifying them. Check their respective manual pages (linked for your convenience).