1

I have a problem in my condition in the following where clause (on the last line):

create procedure getSubjects(IN excludeName VARCHAR(255))
begin
    select *

    from subject

    where excludeName = '' or subject.name not rlike excludeName;
end

No error is reported when I store the procedure, but I get ERROR 1139 (42000) at line 1: Got error 'empty (sub)expression' from regexp whenever I call the procedure without empty parameters.

How can I write this clause?

Note:

I get the same error with the following syntax:

create procedure getSubjects(IN excludeName VARCHAR(255))
begin
    select *
    from subject
    where 
        case excludeName
            when '' then 
                subject.name rlike '.*' 
            else
                subject.name not rlike excludeName
            end;
end
arthur.sw
  • 9,516
  • 7
  • 34
  • 82
  • 1
    Revert the condition: `if(includeName != '', includeName, '.*')`. Else section will be applied to both empty string (condition is false) and NULL (condition is NULL which is treated as false). The same with second condition. – Akina Sep 24 '20 at 09:28

2 Answers2

1

I ended up using a regexp which never matches anything (^\b\B$) giving the following syntax:

create procedure getSubjects(IN excludeName VARCHAR(255))
begin
    select *
    from subject
    where subject.name not rlike if(excludeName is null or excludeName = '', '^\b\B$', excludeName);
end
arthur.sw
  • 9,516
  • 7
  • 34
  • 82
0

Use IFNULL() to replace a null value with a default.

create procedure getSubjects(IN includeName VARCHAR(255), 
                             IN excludeName VARCHAR(255))
begin
    select *
    from subject
    where subject.name rlike if(IFNULL(includeName, '') = '', '.*', includeName)
    and (IFNULL(excludeName, '') = '' or subject.name not rlike excludeName);
end
Barmar
  • 596,455
  • 48
  • 393
  • 495