Re: Extract the beginning of a string

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:<h9GdnewQdfwrejeiRVn-iw_at_comcast.com>...
> or, if the last portion is all numeric
>
> rtrim( theString, '0123456789')
>
> or if, the last portion is not all numeric
>
> substr(
> theString
> ,1
> ,instr(
> translate(
> theString
> ,'0123456789'
> ,'0000000000'
> )
> , '0'
> ) -1
> )
>
> translate replaces all digits with '0'
> instr finds the position of the first '0'
> this decremented and used as the length for substr
>
> if the numeric part is optional, you'll need to add a decode to check for an
> instr value of 0
>
> whatever you do, consider creating a stored function rather than putting
> this expression directly into your SQL statement
>
> --
> Mark C. Stock
> email mcstock -> enquery(dot)com
> www.enquery.com
> (888) 512-2048
>
>

Hi Mark, you guessed right,

my string contains more than just text; a real example would like as 'AMM05-10-00-210-801-A01'; your solution worked perfectly.

Thank you

Bernard Drolet

PS: I implemented it in a function, as suggested ;-) Received on Fri Nov 07 2003 - 18:05:40 CET