Geek Answers Handbook

MySQL REGEXP: matching empty records

I have this SQL condition that should retrieve all rows that satisfy the given regular expression condition:

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

However, I need to add a template to retrieve all the values ​​of the empty country. I am currently using this condition

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

How to achieve the same effect without including the OR clause?

Thanks Erwin

+5
source share
3 answers

This should work:

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

However, in terms of performance, you can use the IN syntax

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

It should be faster later, since REGEXP can be quite slow.

+5
source

, $ ( ), " "...

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

+5

:

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

| France, , ^$, country = ''.

:, , :

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

, : ^(USA|Italy|France|.{0})$, , . ^(USA|Italy|France)$|^.{0}$ .

:

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

, , .

If you want to handle empty values ​​the same way (for example, 0 spaces and 5 spaces are considered empty), you should use a regex:

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

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

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

More articles:


All Articles