This content has been marked as final. Show 10 replies
Input value : 12345
Select should return the value as : 123.45
12345/100?1 person found this helpful
select number / 100 from table;
Edited by: user648773 on 03/04/2013 23:55
this will work if the values is of 5 bytes. However requirement says "If the value received with no decimal then insert decimal before last 2 bytes". In case I receive input as 1234567, this should retrieve as 12345.67. So irrespective of the length , if there is no decimal point then decimal should be inserted before last 2 bytes.
Is the data a number in string format?1 person found this helpful
If not just use
with q as (select '12345' myNum from dual union all select '45.78' from dual) select case when instr(myNum, '.') > 0 then to_number(myNum) else to_number(substr(myNum, 1, length(myNum) - 2) || '.' || substr(myNum, length(myNum) - 1)) end newNum from q NEWNUM 123.45 45.78
with q as (select 12345 myNum from dual union all select 45.78 from dual) select case when instr(myNum,'.')>0 then myNum else myNum/100 end myNum from q MYNUM 123.45 45.78
when instr(number,',')>0 then to_number(number)
Edited by: user648773 on 04/04/2013 00:16 It seems to be string the column. All records have to be the same data type, in this case, number.
997936 wrote:It should work for any number of digits..
this will work if the values is of 5 bytes.
select 1234567/100 from dual;
SELECT DECODE (INSTR (1234567, '.'), 0, 1234567 / 100, 1234567) FROM DUAL;
Edited by: Manik on Apr 4, 2013 8:53 AM
The input is string or number?
The output you need is string or number?
It is my mistake..! 1234/100 will work.
the answer you have provided work correctly with input value exception as 100..!
thanks a lot
Edited by: 997936 on Apr 3, 2013 8:29 PM
@rp0428 and @Manik,
Thanks much!! the solution you provided working correctly.. !
both are numeric.