Find value that is not a number or a predefined string

Question

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?

Salman Arshad · Accepted Answer · 2021-12-01 12:29:24Z

1

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.

edited Dec 1, 2021 at 12:29

answered Dec 1, 2021 at 12:24

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Comments

Marmite Bomber · Accepted Answer · 2021-12-01 12:50:25Z

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

MT0 · Accepted Answer · 2021-12-01 12:53:35Z

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

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

db<>fiddle here

Collectives™ on Stack Overflow

Find value that is not a number or a predefined string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related