Replace the characters with a numeric values

Database

Replace the characters with a numeric values

Hi,

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

Col1:

ahgsjk345hhg67

jhgjaskjjlkx6789hj

i want to replace the characters with null values. Please help me to fix the issue ASAP.

Thanks,

Narasimha

3 REPLIES

Re: Replace the characters with a numeric values

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

4356

can anyone help us with otranslate function and regexp_replace to achive above solution?

Thanks,

Narasimha

Senior Apprentice

Re: Replace the characters with a numeric values

Hi Narasimha,

oTranslate tarnslates individual characters, not ranges. 

This removes all non-digits:

regexp_replace('surya hjaukki 43516', '[^0-9]', '');

This returns 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:

regexp_substr('ahgsjk345hhg67', '[0-9]+')

Re: Replace the characters with a numeric values

Hi Dieter,

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

o/p: 98765

Thanks,

Narasimha