Remove Chars and get only numeric values

Database

Remove Chars and get only numeric values

Hi,

I need to remove all the chars from an alpha numeric string ('AL234ps12;') to get 2342 as the result or

fetch 2342 from the an alpha numeric string ('AL234ps12;').

could anyone give me some idea plz.

Thanks & Regards,

Venkatesh Gubba

4 REPLIES

Re: Remove Chars and get only numeric values

string ('AL234ps12;') looks like it contains 1 and 1 is numeric. Do you want 1 also like 1234 from the string?

You can use REGEXP_SIMILAR(str,'^[0-9]+$','c')=1

Re: Remove Chars and get only numeric values

Source column is having a string value like 'AL234ps12w' and i need the output without characters, that is only numeric values 23412 as the output value to be stored in target column.

N/A

Re: Remove Chars and get only numeric values

There's an old trick utilizing nested oTranslate: remove all characters you want to keep and then use this as characters to be removed:

oTranslate(x, oTranslate(x, '0123456789',''), '')

Re: Remove Chars and get only numeric values

great,

Thanks a lot. :)