MySQL REGEXP: matching blank entries

Question

I have this SQL condition that is supposed to retrieve all rows that satisfy the given regexp condition:

country REGEXP ('^(USA|Italy|France)$')

However, I need to add a pattern for retrieving all blank country values. Currently I am using this condition

country REGEXP ('^(USA|Italy|France)$') OR country = ""

How can achieve the same effect without having to include the OR clause?

Thanks, Erwin

Answer 1

This should work:

country REGEXP ('^(USA|Italy|France|)$')

However from a performance point of view, you may want to use the IN syntax

country IN ('USA','Italy','France', '')

The later should be faster as REGEXP can be quite slow.

Answer 2

There's no reason you can't use the $ (match end of string) to fill in your "empty subexpression" issue...

It looks a little weird but country REGEXP ('^(USA|Italy|France|$)$') will actually work

Answer 3

You could try:

country REGEXP ('^(USA|Italy|France|)$')

I just added another | after France, which should would basically tell it to also match ^$ which is the same as country = ''.

Update: since this method doesn't work, I would recommend you use this regex:

country REGEXP ('^(USA|Italy|France)$|^$')

Note that you can't use the regex: ^(USA|Italy|France|.{0})$ because it will complain that there is an empty sub expression. Although ^(USA|Italy|France)$|^.{0}$ would work.

Here are some examples of the return value of this regex:

select '' regexp '^(USA|Italy|France)$|^$'
> 1
select 'abc' regexp '^(USA|Italy|France)$|^$'
> 0
select 'France' regexp '^(USA|Italy|France)$|^$'
> 1
select ' ' regexp '^(USA|Italy|France)$|^$'
> 0

As you can see, it returns exactly what you want.

If you want to treat blank values the same (e.g. 0 spaces and 5 spaces both count as blank), you should use the regex:

country REGEXP ('^(USA|Italy|France|\s*)$')

This will cause the last row in the previous example to behave differently, i.e.:

select ' ' regexp '^(USA|Italy|France|\s*)$'
> 1