Re: SQL question - trim leading alphabetic characters

[Adam Glauser <adamglauser@xxxxxxxxxxxx>]

Wilt, Charles wrote:
> 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                     

That suggestion gets rid of all the alphabetic characters.  Also, I 
forgot that dashes might be in there as well.

input			result of trim+translate	desired result
ABP-N42A-43030C3D	- 42 -43030 3			42A-43030C3D
ABP-N74B-10801		- 74 -10801			74B-10801
AE1131XMF		1131				1131XMF
AF25033M		25033				25033M
AF25139M		25139				25139M
AF25694			25694				25694
AF364			364				364

FWIW, this SEQUEL query works.  SST is substring, LENGTH finds the 
length of the string, and INDEX is like RPG's check, except that it 
finds the first position of one of the characters in the list, rather 
than _not_ in the list.  I couldn't find an analogue to INDEX in real 
SQL.  I probably just didn't look hard enough :)

SELECT SST(myfield, INDEX(myfield,'0123456789'),LENGTH(prtnum))

Thanks for all the suggestions,
Adam