Hello,
Oracle 11g r2, database column's datatype is number in which i am trying to store numbers.
My scenario,
i have different values ranging from +(positive) to - (negative) (in between +99.99 to -99.99(fix)),when i have + value i need to convert that + into 1 and if i have - then convert - into 0.
Desire output :
if value is...
+11.25 then 11125,(where initial 1 is for +)
+0.50 then 1050,
+0.20 then 1020,
-10.20 then 01020,(where initial 0 is for -)
-0.40 then 0040 and so on...
I have tried below way possible but when it comes to +0.20 or -0.40(in short leading zero) then output becomes like : 120 and 040 respectively which is wrong the correct output should be 1020 and 0040 respectively.
SELECT 006
||
TRIM (
DECODE (
SIGN (ABS (+0.25)),
-1, 0
|| TRIM (
REGEXP_REPLACE (ABS (+0.25),
'[^[:alnum:]'' '']',
'')),
1, 1
|| TRIM (
REGEXP_REPLACE (ABS (+0.25),
'[^[:alnum:]'' '']',
'')),
0
|| TRIM (
REGEXP_REPLACE (ABS (+0.25),
'[^[:alnum:]'' '']',
''))))
FROM DUAL
Can someone guide how to preserve leading zero value.