Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Smile Unanswered: Leading zeros during concatanation

    I have Table called TEST having three colummns
    cola VARCHAR2(10)
    colb number(3)
    colc number(4)

    >>> i need to update all the records of colA where the record is blank and have to update it to corresponding concatinated value of colB + ColC.

    the expected output is :-
    before
    Cola colb colc
    12 34

    after sql execution
    cola colb colc
    0120034 12 34

    >>>I want the leading zeros in the final output.

    -----------------------------------------------------

    I am using the concat operator. But i am not able to put the leading zeros.

    update TEST
    set cola = concat(colb,colc)
    where TEST.cola = ' ';

    The output is :-
    cola colb colc
    1234 12 34

    but i want leading zeros as mentioned above.
    Please Help... Please let me know if you need more info
    -------------------------------------------------------
    Cheers!
    Subho
    Last edited by subhotech; 05-19-10 at 09:03. Reason: display not proper

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try converting the numbers to string, then concat

    Dave

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Specifically
    Code:
    SELECT to_char(colb, '000') || to_char(colc, '0000')

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Just to give you another variation:
    Code:
                          colb                              colc
    select lpad( to_char( 003 ), 3, '0' ) || lpad( to_char( 004 ), 4, '0') as "Result" from dual
    Result
    0030004

  5. #5
    Join Date
    Jan 2010
    Posts
    64

    Many Many Thanks!!

    Many Many Thanks!! ..... Its working

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •