Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Looping

Re: Looping

From: <DPDesroches_at_gmail.com>
Date: 5 Feb 2007 06:55:56 -0800
Message-ID: <1170687356.676360.322710@j27g2000cwj.googlegroups.com>


On Feb 5, 8:15 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> On Feb 4, 10:47 pm, DPDesroc..._at_gmail.com wrote:
>
> > I have an SQL questions.
> > I am trying to reset about 28 sequence generators with a looping
> > program; my problem comes when I try to trim the prefix and suffix
> > from the max number in the database.
>
> > Currently I am using a left and right trim as follows. XXX = column
> > name in this example I do know from looking at another table that
> > controls the prefix and suffix that all prefix and suffix are alpha.
> > RTRIM(ltrim(max(XXX),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'ABCDEFGHIJKLMNOPQRSTUVW­­XYZ')
>
> > any help would be great.
>
> Look up the TRANSLATE function in the SQL manual.
>
> Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02
>
> The following statement returns a license number with the characters
> removed and the digits remaining:
>
> SELECT TRANSLATE('2KRW229',
> '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
> "Translate example"
> FROM DUAL;
>
> Translate example
> -----------------
> 2229
> <<
>
> If the length of the prefix and suffix are fixed then you can just
> SUBSTR the numer digits out by starting at prefix + 1 and ending at
> LENGTH - length of suffix.
>
> HTH -- Mark D Powell --

My first thought was to substr but I have 30+ sequence generators that I will need to loop to a new value and most of them have a prefix, suffix or both and each has a different length, the only common element between all of them is the fact that the prefix and suffix are alpha characters. I was hopping there was something that would reference all the alpha letter without typing them out. Received on Mon Feb 05 2007 - 08:55:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US