Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: help with concatinate

    I have a field of 12 digits its a string.
    some of the data in the field contains
    only 9 digits, some 10, some 11

    How can I update that string to 12 digits
    and have the first digits of the unmatch to 12 digits string
    have zeros infront
    Like:

    if it is '111111111' - to be '000111111111'
    same for 10 dogits string and for 11 digits string


    Thank you

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: help with concatinate

    select lpad(col,12,'0') from table;

  3. #3
    Join Date
    Dec 2003
    Posts
    7
    update test a
    set a.UPC = (select concat('0', aa.UPC)
    from test aa
    where length(rtrim(aa.UPC)) = 11)
    where length(rtrim(a.UPC)) = 11


    when its set to 9 its working, when its set to 1 or 11
    it gives me an error

    ERROR at line 2:
    ORA-01427: single-row subquery returns more than one row

    Please help

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    You are making it too hard. Just like Tony (andrewst) said:

    select lpad(col,12,'0') from table

    or as you tried to demonstrate

    update test
    set UPC = lpad(UPC,12,'0')

    This will prepend 0s (zeros) to the front of every field that is not 12 in length. The strings that are 12 in length are unchanged.

    HTH

  5. #5
    Join Date
    Dec 2003
    Posts
    7
    Thank you, it worked

Posting Permissions

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