1

Hope you're doing well. I have a table with this structure :

Table1(Cust_No    Varchar2(50), 
       First_Name Varchar2(50), 
       Last_Name  Varchar2(50), 
       Error_Code number(1), 
       Error_Desc Varchar2(50))

At first , data will be inserted into column Cust_No and I should Update other columns of the table based on this column.The point is that all cust_No must have number format. For example Cust_No ='aa674' does not have a correct format. I need to write a query to update the table and set column First_Name='--' , columnLast_Name = '--', column Error_Code=1 and column Error_Desc='Incorrect format' for those Cust_No which has incorrect formats. I wanted to use TO_NUMBER() function but it gives me invalid number error. how may I solve this problem? Thanks in advance

2

You may use UDF to detect if a value can be treated as a number. Something like

CREATE OR REPLACE FUNCTION check_for_number( check_value IN VARCHAR2 )
RETURN VARCHAR2 DETERMINISTIC
IS
    tmp NUMBER;
BEGIN
    IF check_value IS NULL THEN
        RETURN 'Z';
    ELSE
        tmp := TO_NUMBER( check_value );
        RETURN 'Y';
    END IF;
EXCEPTION
    WHEN value_error THEN
        RETURN 'N';
    WHEN OTHERS THEN
        RETURN '?';
END check_for_number;

with test as ( select 1 id, '123' val from dual union all
               select 2   , '123asd'  from dual union all
               select 3   , 'asd'     from dual union all
               select 4   , NULL      from dual           )
select id, val, check_for_number(val)
from test;

ID  VAL     CHECK_FOR_NUMBER(VAL)
1   123     Y
2   123asd  N
3   asd     N
4   -       Z
  • 1
    Thanks but can you show me how to use regexp to handle this? I think It might be easier to use regexp rather than function – Pantea Tourang Sep 3 at 5:32
3

As per your comment, requesting a regex solution:

update table1 set 
  error_code = 1
, error_desc = 'Incorrect format' 
where not regexp_like( cust_no, '^[0-9]+$' )

However, I would suggest that this is too little, too late.

You should not be allowing this defective data into the database in the first place! (Unless this is part of a migration exercise and this is a transitional table).

Your application should be detecting this bad formatting and not even saving the record to the database.

Second Thought:

Alternatively, put the check into the table definition

[Air Code; untested]

create table Table1
( Cust_No Varchar2(50) 
, CHECK( regexp_like( Cust_No '^[0-9]+$' )
. . . 

Third Thought:

If you customer number is, indeed, a number, then why are you storing it in the wrong Data Type?

create table Table1
( Cust_No  NUMBER 
. . . 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.