how to handle null value in the table in oracle

Question

I have a table having one field "SUSCRIBER_ID" and structure of the table is that SUSCRIBER_ID field should not be null. Now, i want to update the value of that field with below conditions.

UPDATE table_name SET SUSCRIBER_ID = DECODE (REGEXP_REPLACE (SUSCRIBER_ID,'^\D*',NULL),NULL,SUSCRIBER_ID);

error is coming name SUSCRIBER_ID can not be null however when REGEXP_REPLACE is return the null value then i am skipping the records

data is in the table

name
---------
sumit123sumit

Please assist

Maheswaran Ravisankar · Accepted Answer · 2014-02-27 17:37:13Z

1

UPDATE table_name SET SUSCRIBER_ID = DECODE (REGEXP_REPLACE (SUSCRIBER_ID,'^\D*',NULL),
                                             NULL,
                                             SUSCRIBER_ID,
                                             REGEXP_REPLACE (SUSCRIBER_ID,'^\D*',NULL))

I believe you need a final default argument with the REGEXP_REPLACE() itself.

As @AlexPoole said , Other way is using NVL()

UPDATE table_name SET SUSCRIBER_ID = NVL (REGEXP_REPLACE (SUSCRIBER_ID,'^\D*',NULL),
                                          SUSCRIBER_ID);

edited Feb 27, 2014 at 17:37

answered Feb 27, 2014 at 17:23

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

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

1 Comment

Alex Poole Over a year ago

To avoid repetition I'd make that NVL(REGEXP_REPLACE (SUSCRIBER_ID,'^\D*',NULL), SUSCRIBER_ID), but some people prefer the clarity of decode or case...

Collectives™ on Stack Overflow

how to handle null value in the table in oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related