Find value that is not a number or a predefined string-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

No need to use regexp (performance will increase by large data) - plain old TRANSLATE is good enough for your validation.

Note that the first translate(column1,'x0123456789','x') remove all numeric charcters from the string, so if you end with nullthe string is OK.

The second translate(lower(column1),'x.','x') removes all dots from the lowered string so you expect the result nv. To avoid cases as n.....v.... you also limit the string length.

select 
  column1,
  case when 
     translate(column1,'x0123456789','x') is null or /* numeric string */
     translate(lower(column1),'x.','x') = 'nv' and length(column1) <= 4 then 'OK' 
  end as status   
from table1

COLUMN1   STATUS
--------- ------
1010101   OK
1012828n    
1012828nv   
n.....v....          
n.V       OK

Test data

create table table1 as
select '1010101' column1 from dual union all -- OK numbers
select '1012828n' from dual union all        -- invalid
select '1012828nv' from dual union all       -- invalid
select 'n.....v....' from dual union all     -- invalid
select 'n.V' from dual;                      -- OK nv

CodePudding user response：

You can use:

select count(*)
from   table1
WHERE  TRANSLATE(column1, ' 0123456789', ' ') IS NULL 
OR     LOWER(column1) IN ('nv', 'n.v', 'nv.', 'n.v.');

Which, for the sample data:

CREATE TABLE table1 (column1) AS
SELECT '12345' FROM DUAL UNION ALL
SELECT 'nv' FROM DUAL UNION ALL
SELECT 'NV' FROM DUAL UNION ALL
SELECT 'nV' FROM DUAL UNION ALL
SELECT 'n.V.' FROM DUAL UNION ALL
SELECT '...................n.V.....................' FROM DUAL UNION ALL
SELECT '..nV' FROM DUAL UNION ALL
SELECT 'n..V' FROM DUAL UNION ALL
SELECT 'nV..' FROM DUAL UNION ALL
SELECT 'xyz' FROM DUAL UNION ALL
SELECT '123nv' FROM DUAL;

Outputs:

COUNT(*)

5

COUNT(*)
5

or, if you want any quantity of . then:

select count(*)
from   table1
WHERE  TRANSLATE(column1, ' 0123456789', ' ') IS NULL 
OR     REPLACE(LOWER(column1), '.') = 'nv';

Which outputs:

COUNT(*)

9

COUNT(*)
9

db<>fiddle here