SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NVL and Translate

556287Dec 14 2007 — edited Dec 14 2007

Hello
I want to insert 0 for the null values and charecter values. Can i simplify this futher more I am on 9i

NVL(NVL2(TRANSLATE (admissionhour, '~1234567890', '~'),NULL,admissionhour),0)

SQL> WITH t AS 
  2     (SELECT 'f' val FROM dual
  3        UNION ALL 
  4      SELECT '12' val FROM dual
  5      UNION ALL
  6   SELECT  NULL val FROM dual)
  7  SELECT val, NVL(NVL2(TRANSLATE (val, '~1234567890', '~'),NULL,val),0)
  8  FROM t           
  9       
SQL> /

VA NV
-- --
f  0
12 12
   0

Thank you

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 11 2008

Added on Dec 14 2007

3 comments

394 views