I have faced one issue like one of the column contains characters in between the numeric values but my target contains the column datatype as decimal, below are the sxample scenario
i want to replace the characters with null values. Please help me to fix the issue ASAP.
i tried with otrnslate function but only one character got replaced.
sel otranslate('surya hjaukki 43516', '[a-z]', '');
we got the output like below,
sury hjukki 43516
but we need the output like below
can anyone help us with otranslate function and regexp_replace to achive above solution?
oTranslate tarnslates individual characters, not ranges.
This removes all non-digits:
regexp_replace('surya hjaukki 43516', '[^0-9]', '');
34567 for your first example, do you really want this result, doesn't look like a correct decimal to me. You might want to extract the first serries of digits instead:
thanks for your quick reply. i have used the below query and got the result as well. I will try with regexp_substr function.
regexp_replace('i am Krishna how are 98765you How old are you', '[^0-9]*' , '', 1,0,'i')