Remove Chars and get only numeric values

Database
Enthusiast

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

7 REPLIES 7
Enthusiast

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

Enthusiast

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.

Ambassador

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',''), '')

Enthusiast

Re: Remove Chars and get only numeric values

great,

Thanks a lot. :)

Enthusiast

Re: Remove Chars and get only numeric values

Hi Dnoeth,

 

 I wants to ignore the rows if we have any characters or bad data and i wants to load the rows if we have completely numbers. Can you please tell me to how to write the query for this?

 sample data: 

1136240  -- this one want to load as is 

the below all are wants to ignore completely. dont want to load these kind of rows. 
eol-s012a-2929 s pennsylvania st 

NE4109 10767424 201766

sv042x
Tri-Cities
Work Order - 05-36-48 07-12-2019
testjim
y6
y6 sc543 10765838

 

Thanks

Highlighted
Ambassador

Re: Remove Chars and get only numeric values

Either TO_NUMBER(mycol), instead of failing it returns NULL for invalid data.

Or similar TRYCAST(mycol AS INT), but it returns 0 for values like '' or '.'

 

Enthusiast

Re: Remove Chars and get only numeric values

thanks