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

Query question

Expand Messages
  • Paul
    Hello all: I am an Oracle SQL neophyte and need to be able to dothe following: My customer used to send in a location code of up to 4 characters with leading
    Message 1 of 5 , Nov 9, 2009
    • 0 Attachment
      Hello all:

      I am an Oracle SQL neophyte and need to be able to dothe 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.

      Thank you in advance for any assistance you can provide.

      Regards,

      Paul
    • Tim Mitchell
      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
      Message 2 of 5 , Nov 9, 2009
      • 0 Attachment
        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.

        hth,
        Tim

        On Mon, Nov 9, 2009 at 8:30 AM, Paul <tophamp@...> wrote:

        >
        >
        > Hello all:
        >
        > I am an Oracle SQL neophyte and need to be able to dothe 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.
        >
        > Thank you in advance for any assistance you can provide.
        >
        > Regards,
        >
        > Paul
        >
        >
        >



        --
        Tim Mitchell
        www.TimMitchell.net
        www.Tyleris.com


        [Non-text portions of this message have been removed]
      • tophamp@yahoo.com
        Thanks!! Sent via BlackBerry by AT&T ... From: Tim Mitchell Date: Mon, 9 Nov 2009 19:18:21 To: Subject:
        Message 3 of 5 , Nov 10, 2009
        • 0 Attachment
          Thanks!!
          Sent via BlackBerry by AT&T

          -----Original Message-----
          From: Tim Mitchell <tdmitch@...>
          Date: Mon, 9 Nov 2009 19:18:21
          To: <SQLQueriesNoCode@yahoogroups.com>
          Subject: Re: [SQLQueriesNoCode] Query question

          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.

          hth,
          Tim

          On Mon, Nov 9, 2009 at 8:30 AM, Paul <tophamp@...> wrote:

          >
          >
          > Hello all:
          >
          > I am an Oracle SQL neophyte and need to be able to dothe 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.
          >
          > Thank you in advance for any assistance you can provide.
          >
          > Regards,
          >
          > Paul
          >
          >
          >



          --
          Tim Mitchell
          www.TimMitchell.net
          www.Tyleris.com


          [Non-text portions of this message have been removed]



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

          Yahoo! Groups Links
        • caj_y
          Hi Paul, In oracle SQL there are functions to pad values from LEFT or RIGHT LPAD( , , )
          Message 4 of 5 , Nov 28, 2009
          • 0 Attachment
            Hi Paul,

            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;

            Regards
            Chetan

            =========================================================


            --- In SQLQueriesNoCode@yahoogroups.com, Tim Mitchell <tdmitch@...> wrote:
            >
            > 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.
            >
            > hth,
            > Tim
            >
            > On Mon, Nov 9, 2009 at 8:30 AM, Paul <tophamp@...> wrote:
            >
            > >
            > >
            > > Hello all:
            > >
            > > I am an Oracle SQL neophyte and need to be able to dothe 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.
            > >
            > > Thank you in advance for any assistance you can provide.
            > >
            > > Regards,
            > >
            > > Paul
            > >
            > >
            > >
            >
            >
            >
            > --
            > Tim Mitchell
            > www.TimMitchell.net
            > www.Tyleris.com
            >
            >
            > [Non-text portions of this message have been removed]
            >
          • 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 5 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.