Find value that is not a number or a predefined string

I have to test a column of a sql table for invalid values and for NULL.

Valid values are: Any number and the string ‘n.v.’ (with and without the dots and in every possible combination as listed in my sql command)

So far, I’ve tried this:

select count(*)
from table1
where column1 is null
or not REGEXP_LIKE(column1, '^[0-9,nv,Nv,nV,NV,n.v,N.v,n.V,N.V]+$');

The regular expression also matches the single character values ‘n’,’N’,’v’,’V’ (with and without a following dot). This shouldn’t be the case, because I only want the exact character combinations as written in the sql command to be matched. I guess the problem has to do with using REGEXP_LIKE. Any ideas?

Answer

I guess this regexp will work:

NOT REGEXP_LIKE(column1, '^([0-9]+|n.?v.?)$', 'i')

Note that , is not a separator, . means any character, . means the dot character itself and 'i' flag could be used to ignore case instead of hard coding all combinations of upper and lower case characters.