1

I was wondering if it is possible to pass parameters to regular expressions as if they were literal strings in the MySQL REGEXP function. What I would like to do is the following:

SELECT ? REGEXP CONCAT('string', ?, 'string')

Now when I pass a dot (".") to the second parameter, it will automatically match any character, as expected. This means that strings like "stringastring" and "stringbstring" match the pattern. I wondered if it is possible to match the literal dot only, so as to only match "string.string" in this case. Is there a way to do such a thing with a MySQL regular expression, that does not involve explicitly escaping the parameter (which defeats the purpose of passing parameters in this first place)?

user2180613
  • 739
  • 6
  • 21

3 Answers3

0

Try putting brackets, as in:

SELECT ? REGEXP CONCAT('string', '[.]', 'string')

See here: http://sqlfiddle.com/#!2/a3059/1

JamesJ
  • 13
  • 3
  • Quite frankly this doesn't answer the question. The hack isn't applicable either, because I don't know the length of the parameter I am going to pass. – user2180613 Dec 21 '13 at 15:41
  • I don't think you can use regular expressions without explicitly escaping the special character. See the link that I put. – JamesJ Dec 21 '13 at 15:48
0

If I understand your question correctly I think you are looking for this:

SELECT ? REGEXP CONCAT('string', REPLACE(?, '.', '[.]'), 'string')

using the REPLACE function, any dot is always escaped to [.], but all others special characters are passed literally.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • No I intend to pass parameters to the regular expression for it to interpet them as literals. If that dot were a ^ or a series of characters.. hacks such as the one you suggest don't apply. – user2180613 Dec 21 '13 at 19:14
  • @user2180613 but why you need a regular expressions and not just a LIKE or a = ? – fthiella Dec 21 '13 at 20:34
  • Well because the use case requires a regular expression? And it's a quite complicated one too. – user2180613 Dec 21 '13 at 20:42
  • @user2180613 okay so the two strings are two regular expressions, it's just the parameter that has to be matched literally? – fthiella Dec 22 '13 at 10:20
0

For those who are using PHP and looking for answer related to this I found an answer related to this in stackover here, though not directly for MySQL queries. The idea is to use preg_quote() to escape regexp meta characters. But in your case you have to apply it twice to your parameter.

$param = preg_quote(preg_quote($param))

Then

SELECT ? REGEXP CONCAT('string', $param, 'string')

Read this article and the comments to find out more

Community
  • 1
  • 1
RLVillacalos
  • 41
  • 4