Re: Validate numeric values in strings

"Hendrik Lampert" wrote...
> Hi,
>
> i 'm trying to extract a decimal value out of a string.
>
> string_a: 'ab 123 cde-tt_rr'
> string_b: 'ab123 cde-\#xx'
>
>
> each valid numeric value must be separated by spaces. the substr 123 of
> string_a is valid and easy to check . how can i validate string_b?
string_b
> does not contain any valid numeric value, because a space is missing. The
> Oracle function to_number runs into an error.
>
> Thanks
> Hendrik
>

Hi Hendrik,

if there is only one number part in the string you can use this (thanks again, Andrew):

select TRANSLATE( 'ab123 cde-\#xx', '0' || TRANSLATE( 'ab123 cde-\#xx' , '#abcdefghijklmnopqrstuvwxyz0123456789', '#abcdefghijklmnopqrstuvwxyz' ), '0' )
from dual

Sorry for the bad formatting.

Greetings and hth,
Guido Received on Wed Apr 23 2003 - 06:23:00 CDT