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'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
>
> > 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
