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.

Neo · Accepted Answer · 2012-01-27 13:09:07Z

4

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);

edited Jan 27, 2012 at 13:09

answered Jan 27, 2012 at 13:01

Neo

2,4054 gold badges42 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eduard Luca Over a year ago

Yup, just figured it out. Something very similar to your solution, I'm sure yours works too.

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

Jon Menard · Accepted Answer · 2024-11-26 14:19:56Z

0

In Drupal 8+ you can do the following:

$regex = '[\W]';
$query->addExpression("column, :regex, '')", 'alias', [':regex' => $regex]);

answered Nov 26, 2024 at 14:19

Jon Menard

3863 silver badges15 bronze badges

Collectives™ on Stack Overflow

MySQL query with regexp not working in Drupal

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related