Re: Looping

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 5 Feb 2007 06:15:50 -0800
Message-ID: <1170684950.696839.40610@s48g2000cws.googlegroups.com>

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 -- Received on Mon Feb 05 2007 - 08:15:50 CST