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,
string ('AL234ps12;') looks like it contains 1 and 1 is numeric. Do you want 1 also like 1234 from the string?
You can use
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.
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',''), '')
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?
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
Work Order - 05-36-48 07-12-2019
y6 sc543 10765838
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 '.'