0

I'm trying to conditionally match against a REGEX. If the REGEX is blank, it is a match, otherwise, match against the REGEX. Currently I have tried the mySQL IF function, CASE statement, and logical operators with no success. This is on MySQL 5.6.12. The error I am getting is: 

ERROR 1139 (42000): Got error 'empty (sub)expression' from regexp

Here are some statements showing my issue.

SET @test = '';
SELECT (@test = '' || ('abc123' REGEXP @test)) AS matches;
SELECT (true || ('abc123' REGEXP @test)) AS matches;
SELECT IF(true, true, 'abc123' REGEXP @test) AS matches;
SELECT (CASE WHEN true THEN true ELSE 'abc123' REGEXP @test END) AS matches;

I would have expected the operators precedence order to return true in all these statements. Is there documentation I missed? Any help is appreciated.

Improve this question
2
  • 1
    The double vertical bar in standard-compliant SQL means concatenation (sticking strings together), although you are correct that by default MySQL treats it as disjunction (logical OR). Commented Dec 12, 2013 at 16:15
  • 1
    @ajm475du Good to know -- thanks! For disambiguation purposes, it seems valuable always to use OR in place of the disjunctive ||, and, in SQL dialects offering it, the CONCAT function in place of the concatenative ||. Commented Dec 12, 2013 at 16:19

3 Answers 3

Reset to default
4

Your problem here is that the REGEXP operator gets evaluated before whatever encloses it, and won't accept the empty string as a valid pattern. On the other hand, per the MySQL REGEXP operator's documentation, it will accept NULL as a valid pattern, which always causes REGEXP to return NULL -- and you can pass the result of an IF as a REGEXP pattern. So:

SELECT IF("abc123" REGEXP IF(@test = '', '.*', @test), true, false) AS matches;

The inner IF() ensures that REGEXP sees NULL, which is a valid pattern, in place of the empty string, which is not; the outer IF() casts the result of the REGEXP evaluation, which will be NULL if the pattern it's passed is NULL, to the Boolean value you need returned under the 'matches' alias.

EDIT: I misread the question; the intent is that when the pattern is the empty string, it should be treated as a match, which my original answer failed to do. I've updated the answer to replace the inner IF()'s NULL with the pattern '.*', which matches every possible value (save NULL, which matches nothing) -- this brings the result in line with the asker's intent.

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

1 Comment

Thanks for the quick response. I did not notice the part of the documentation concerning using NULL in REGEXP. I ended up settling on (@test IS NULL || 'abc123' REGEXP @test). Using NULL benchmarked ~17% faster than using '.*'. Hopefully this will help anyone else with this issue.
0 
SELECT IF(@test = '', true, 'abc123' REGEXP @test) AS matches
Improve this answer

5 Comments

MySQL 5.1.66: set @test = ''; select if(@test = '', 1, "omg" regexp @test) as matches; => ERROR 1139 (42000): Got error 'empty (sub)expression' from regexp
Works for me on 5.1.69. What exactly is your regex? Probably there's a syntax error in the pattern itself, not in the sql surrounding it
Evidently the asker is using some version prior to that, then. But there's a way; see the answer I'm working on right now.
Ah yes, I see now. if the pattern is an empty string, then I get that error. I guess the parser's compiling the pattern regardless of whether the if() would let it execute anyways, and an empty pattern isn't allowed.
Exactly. But NULL is a valid pattern, and you can pass the result of an IF() evaluation as a REGEXP pattern, so it's a trivial trick to ensure that REGEXP never sees an empty string it's not prepared to handle -- again, see my answer.
-1

Its too late for the reply but anyone looking from the future, here is similar way to do it

SELECT IF(column_1 REGEXP '^[a-z][0-9]+$', true, false) AS matches;

You should use proper regex pattern for your use case to define absolute value which will match all the value in column_1 and give true if it matches all esle false. Regex pattern can be defined in such a way that it can accept null as well if required.

You can try few regex pattern here to test https://regex101.com/

Or another option would be to use CASE statement with REGEXP and get different values based on case condition

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.