RE: SQL question - trim leading alphabetic characters

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Actually Charles, I think you may be on the right track for the task at
hand.  I believe Adam said he just wants to SORT based on numeric value.

So, for ordering only, something like this may suffice:

SELECT * FROM myLib/myFile 
ORDER BY TRIM(TRANSLATE(myField,' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))

Adam, works?  
Perhaps ASCending versus DESCending matters? 
And maybe changing ' ' as replacement character into something more inline
with number sorting?

Elvis


Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
http://centerfieldtechnology.com/training.asp


-----Original Message-----
Subject: RE: SQL question - trim leading alphabetic characters

ignore my post...

It gets rid of all letters....

Charles 

> -----Original Message-----
> Subject: RE: SQL question - trim leading alphabetic characters
>
> Adam,
>
> Why don't you use Elvis idea?
>
> I'm on v5r2, so I can't use replace, but TRANSLATE works...
>
> select trim(translate(myfield,' ', 
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))  
> from myfile                     
>
>
> Charles