CODEWITHSUNDEEP
sqloracle-database
Lid
Lid

Reputation: 25

find invalid characters in string

I need a select statement that will show any invalid characters in Customer number field.

A vaild customer number starts with the captial letter N then 10 digits, can be zero to 9.

Something like,

SELECT (CustomerField, 'N[0-9](10)') <> '' 
FROM CustomerTable;

Upvotes: 1

Views: 7197

Answers (3)

user5683823
user5683823

Reputation:

If you really need to find the invalid characters in the string (and not to just simply find the strings that are invalid) perhaps this more complex query will help. You didn't state in what format you may need the output, so I made up my own. I also created several strings for testing (in particular, it is always important to check that the NULL input is treated correctly).

The column len shows the length of the input, if it's not 11. The length of the empty string (null in Oracle) is shown as 0. The first-nondigit columns refer to characters starting at the SECOND position in the string (ignoring the first character, for which the rules are different and which is checked for validity separately).

with
     inputs ( str ) as (
       select 'N0123456789' from dual union all
       select ''            from dual union all
       select '02324434323' from dual union all
       select 'N02345678'   from dual union all
       select 'A2140480080' from dual union all
       select 'N93049c4995' from dual union all
       select 'N4448883333' from dual union all
       select 'PAR3993949Z' from dual union all
       select 'AN39E'       from dual
     )
-- end of test data; query begins below this line
select str,
       case when regexp_like(str, '^N\d{10}$') then 'valid'
            else 'invalid'                   end         as classif,
       case when length(str) != 11 then length(str)
            when str is null       then 0    end         as len,
       case when substr(str, 1, 1) != 'N' 
                      then substr(str, 1, 1) end         as first_char,
       regexp_substr(str, '[^0-9]', 2)                   as first_nondigit,
       nullif(regexp_instr( str, '[^0-9]', 2), 0)        as first_nondigit_pos
from   inputs
;

OUTPUT

STR          CLASSIF    LEN  FIRST_CHAR FIRST_NONDIG FIRST_NONDIGIT_POS
-----------  -------  -----  ---------- ------------ ------------------
N0123456789  valid
             invalid      0
02324434323  invalid         0
N02345678    invalid      9
A2140480080  invalid         A
N93049c4995  invalid                    c                             7
N4448883333  valid
PAR3993949Z  invalid         P          A                             2
AN39E        invalid      5  A          N                             2

9 rows selected.

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44981

\d stands for digit

Perl-influenced Extensions in Oracle Regular Expressions

The rest if the regular expression elements can be found here

Regular Expression Operator Multilingual Enhancements

select  *
from    CustomerTable
where   not regexp_like (CustomerField,'^N\d{10}$')

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use regexp_like.

select customerfield
from CustomerTable
where not regexp_like(CustomerField, '^N[0-9]{10}$')

This will show the customerfield's that don't follow the pattern specified.

Upvotes: 3

Related Questions