1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Filling empty columns with a string of zeros

Discussion in 'SQL PL/SQL' started by awdigrigoli, Feb 18, 2011.

  1. awdigrigoli

    awdigrigoli Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have a result set that will be exported to a text file. The data in the text file is determined by column so any empty columns in the table need to be a full string of zeros instead of empty space.
    My question is, what is the fastest way to fill the empty talbe columns with zeros?

    A trigger?

    Some default column setting I am not aware of?

    Running an update to fill only empty columns with the zeros after I perform the first update?

    Is there a way to fill with zeros during the update?


    Thank you,
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Why not using NVL function to return NULL with '000' while forming the result string for export?

    Code (SQL):

    SQL>  SELECT * FROM dept;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            50 MARKETING
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL> SELECT DEPTNO,DNAME,NVL(LOC,'000')LOC FROM dept;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            50 MARKETING      000
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>