Loading ...
Sorry, an error occurred while loading the content.

Re: Query question

Expand Messages
  • Peter Nilsson
    ... This should be avoided in join conditions unless the padding will produce an indexed key. For instance, in Australia, post podes are numbers less than
    Message 1 of 5 , Nov 29, 2009
    • 0 Attachment
      --- In SQLQueriesNoCode@yahoogroups.com, "caj_y" <caj_y@...> wrote:
      > Tim Mitchell <tdmitch@> wrote:
      > > On Mon, Nov 9, 2009 at 8:30 AM, Paul <tophamp@> wrote:
      > > > I am an Oracle SQL neophyte and need to be able to do the
      > > > following:
      > > >
      > > > My customer used to send in a location code of up to 4
      > > > characters with leading zeros - 0009, 0099, 0999, 9999, etc.
      > > > They have changed their system to now truncate leading zeros
      > > > on all locations greater than 2 digits in length.
      > > > My ERP has their locations set up as 4 digits with leading
      > > > zeros. How can I, in SQL, evaluate their locations and
      > > > prepend the appropriate number of leading zeros so their
      > > > orders will process? Our system admins do not wish to modify
      > > > all their location codes.
      > >
      > > You can use the RIGHT function to accomplish this. Assuming
      > > your location code column is character data, use something
      > > similar to the following:
      > >
      > > RIGHT('0000' + original_data, 4)
      > >
      > > This will pad the column named [original_data] with leading
      > > zeroes so that the values are exactly four characters in length.
      >
      > In oracle SQL there are functions to pad values from LEFT or RIGHT
      >
      > LPAD(<truncated_value>,<required_size>,<character_to_pad_with>)
      > RPAD(<truncated_value>,<required_size>,<character_to_pad_with>)
      >
      > Try the command
      >
      > select lpad(832,6,'0') from dual;
      > Output : 000832
      >
      > select rpad(2500,6,'*') from dual;
      > Output : 2500**
      >
      > In your case it will be
      >
      > Select lpad(locCode,4,'0') from locTable;

      This should be avoided in join conditions unless the padding will
      produce an indexed key.

      For instance, in Australia, post podes are numbers less than
      10,000. They are often stored as indexed NUMBER(4). Doing a join
      like...

      select blah
      from table_a a
      join post_codes pc
      on lpad(pc.post_cd, 4, '0') = a.post_cd_text
      and pc.suburb_ds = a.suburb_ds

      ...will perform badly if post_codes.post_cd is a number. In such
      cases, better is...

      select blah
      from table_a a
      join post_codes pc
      on pc.post_cd = to_number(a.post_cd_text)
      and pc.suburb_ds = a.suburb_ds

      Although, if table_a.post_cd_text potentially has fields that
      can't be converted to a number, then you need to take extra care.

      I've encountered cases like this reporting on Trim Context
      (records management software) that often stores numeric record
      ID references in text fields.

      --
      Peter
    Your message has been successfully submitted and would be delivered to recipients shortly.