All Forums Database
12 May 2016
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
 

12 May 2016

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

dnoeth 4628 posts Joined 11/04
12 May 2016

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

 

Dieter

12 May 2016

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

You must sign in to leave a comment.