Mark D Powell 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 --
Also use TRIM rather than LTRIM(RTRIM( when you need "trim" functionality.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Feb 05 2007 - 12:01:07 CST
