MySQL query with regexp not working in Drupal

Question

I have the following query, courtesy of SO:

SELECT field_website_value FROM field_data_field_website  WHERE field_website_value NOT REGEXP('^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}(/\S*)?') AND field_website_value!=''

When executing this query directly in the MySQL client, it works (shows the values that don't match the pattern).

However when putting it in Drupal, it stops working, it just returns the rows which are not empty.

$query = "SELECT field_website_value FROM field_data_field_website  WHERE field_website_value NOT REGEXP('^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}(/\S*)?') AND field_website_value!=''";
$res = db_query($query)->fetchAll();
echo count($res);
echo "<pre>";print_r($res);die();

Is there any way I can use Regexp in Drupal?

Note: getting all rows and applying the regex in PHP isn't an option.

Answer 1

I'm no drupal expert but I bet db_query function is doing a mysql_real_escape_string() call which will mess up the regular expression, are there any other functions you can pass that won't do this?

Actually it is the {} brackets causing the issue, you need to pass the data as a variable,

$query = "SELECT field_website_value FROM field_data_field_website  WHERE field_website_value NOT REGEXP('%s') AND field_website_value!=''";
$regexp = '^(https?://|www\\.)[\.A-Za-z0-9\-]+\\.[a-zA-Z]{2,4}(/\S*)?';
db_query($query, $regexp);