Include null in oracle regex

Question

I've been trying the following but it does not return any (null) values. How can I include null values in using a regular expression with oracle an in fact return everything including null. (It is necessary to use a regex as the value is replaced with a variable in python and so the value will not always be null. Otherwise i could just leave out the expression.)

SELECT * from table WHERE
REGEXP_LIKE (column1,'.*|NULL$')

NULL is not a string. Regex can only match a string pattern. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Apr 8, 2016 at 11:34
so what would be the most elegant way to do it if I wanted to include null? — Nickpick
– Nickpick, Commented Apr 8, 2016 at 11:37
You sem to be looking for null, or any (not-null) value, which makes the check a bit pointless? Or are you saying the regex pattern is coming from a variable, so you won't really just have .*? If so your example pattern is a bit confusing. As is the working you've used. — Alex Poole
– Alex Poole, Commented Apr 8, 2016 at 11:41

Gordon Linoff · Accepted Answer · 2016-04-08 11:41:37Z

1

Two methods.

Explicit comparison:

WHERE (REGEXP_LIKE(column1, '.*') OR column1 IS NULL)

Or replacement:

WHERE REGEXP_LIKE(COALESCE(column1, '<NULL>'), '.*|<NULL>')

The second method is more dangerous, because you could already have the replacement string in the column.

answered Apr 8, 2016 at 11:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

user5683823 Over a year ago

The second method can be made safe by changing the alternative in the regexp from <NULL> to ^<NULL>$

user5683823 Over a year ago

With that said, the first method is both easier to read and understand and more efficient (no call to COALESCE on every row, and no additional string comparison for NULL values).

Alex Poole Over a year ago

@mathguy - you could still get a false match if the column value is actually '<NULL>'; the anchors only stop it matching if that's part of a longer value?

user5683823 Over a year ago

Err... you are correct Sir. Another idea for saving it, just changing the alternative to ^$ (and not wrapping the column in COALESCE), doesn't work either, although based on Oracle's choice to make zero length strings be the same as NULL, it SHOULD work. It just doesn't. So, the first method it is!

Collectives™ on Stack Overflow

Include null in oracle regex

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related