regexp include null in mysql?

Question

Is it possible to do the following in mysql with a single where clause?

WHERE
    employer IS NULL 
 OR employer REGEXP 'employed|indepdendent|self|freelanc'

That is, move the NULL into the regexp?

Regex only checks strings, it can't check other values. Unless you coerce NULL to a 'NULL' string of course. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Feb 8, 2021 at 22:35
Can you elaborate on the use case that gives rise to this requirement to include checking for NULL using RegExp (which, as @WiktorStribiżew points out, generally wouldn't work without coercing it to a string value)? — esqew
– esqew, Commented Feb 8, 2021 at 22:36
@esqew it's just how the data is stored. Sometimes it's null, sometimes empty string, sometimes some values that "mean null", etc. — David542
– David542, Commented Feb 8, 2021 at 22:37
Are the values in employer just the strings employed, indepdendent, self and freelanc or are those just words included in them e.g. 'mostly indepdendent`? — Nick
– Nick, Commented Feb 9, 2021 at 0:23

David542 · Accepted Answer · 2021-02-08 22:37:01Z

1

As suggested in the comment by @Wiktor, the simplest way to do this would be to coerce the field to a string so that the regex can work against a non-null string value.

For example:

WHERE
    COALESCE(employer, 'NULL') REGEXP 'employed|indepdendent|self|null|freelanc'

answered Feb 8, 2021 at 22:37

David542

112k211 gold badges583 silver badges1.1k bronze badges

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

2 Comments

r2evans Over a year ago

I agree that this is likely the best way to do it if you must reduce to a single where clause, but I disagree with the premise that doing this is a good thing. I've had data that included the literal "NULL" string, so to me this technique would have masked that problem. Additionally, the regex you've chosen can over-match when any substring of employer contains 'self' or 'null'. If you are certain that it will never happen, that's fine ... but it never happens until it does. (Somebody specializing in "jury nullification", perhaps?)

David542 Over a year ago

@r2evans ha, jury nullification or any self services -- self help, self-guided...good point, agreed!

Collectives™ on Stack Overflow

regexp include null in mysql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related